# Search, find & display [Solved]

Ask a question noob - Last answered on Nov 29, 2016 at 05:07 PM by learningenquirer
Hello,

I have been manually filling out the week no's, in a budget sheet, but want to try and auto populate. I have a table with dates and Week No's listed against as reference.

I thought it would be easy enough.

Find this date e.g. "sheet 2 B3",

WK # DATE
1 31/12/2015
? 4/01/2016
2 6/01/2016
2 8/01/2016
3 11/01/2016
3 12/01/2016

Search "sheet 1 B2:H6", and return week no reference from A2:A6.

Week No. Start Date
1 27 Dec 15 28 Dec 15 29 Dec 15 30 Dec 15 31 Dec 15 01 Jan 16 02 Jan 16
2 03 Jan 16 04 Jan 16 05 Jan 16 06 Jan 16 07 Jan 16 08 Jan 16 09 Jan 16
3 10 Jan 16 11 Jan 16 12 Jan 16 13 Jan 16 14 Jan 16 15 Jan 16 16 Jan 16
4 17 Jan 16 18 Jan 16 19 Jan 16 20 Jan 16 21 Jan 16 22 Jan 16 23 Jan 16
5 24 Jan 16 25 Jan 16 26 Jan 16 27 Jan 16 28 Jan 16 29 Jan 16 30 Jan 16

I have tried SEARCH, and MATCH functions without success. At this rate it would be easier to manually imput.

Surely someone on here can help me please :)
+0
noob, Good afternoon.

Sheet1 --> B2:I6
Sheet2 --> B3:C8

Try to use:

Sheet2
B3 --> =SUMPRODUCT((Sheet1!\$C\$2:\$I\$6=C3)*(Sheet1!\$B\$2:\$B\$6))

Copy it down.

Is that what you want?
I hope it helps.
--
Belo Horizonte, Brasil.
Marcílio Lobão
+0
(Previously noob)

Thanks for that. I don't think that is what i was after. I don't think i explained it very well.

Second attempt.

Sheet 2 Cell B142 is the cell i am putting my formula in. I am hoping to drag down the formula in column B.

Sheet 1 is my week # reference.

I want the formula to use the date in Sheet 2 Cell C142 (in this case 16/06/2016) to search my list in Sheet 1 and find the date (16 Jun 16 in Cell F110) and the corresponding week. Answering the formula with the week # (25 from Cell A110).

Would love to find a way to make this happen.

+0
learningenquirer, Good morning.

"...Thanks for that. I don't think that is what i was after..."
The formula that was suggested to you is exactly what you need.

"...I don't think i explained it very well..."
Your need is a simple process to understand.

Since you have sent us only one image of part of your spreadsheet, I will adapt the formula by changing the intervals for what we can see.

Sheet2 --> B142

=SUMPRODUCT((Sheet1!\$B\$2:\$H\$111=C142) * (Sheet1!\$A\$2:\$A\$111))

learningenquirer-
thank you so much, it worked!!!! i forget this is not simply an addition formula