Search, find & display [Solved]

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

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",

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 :)
plus moins
noob, Good afternoon.

Supposing your example:
Sheet1 --> B2:I6
Sheet2 --> B3:C8

Try to use:

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
Leave a comment
plus moins
(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.

Thanks in advance. :)
Leave a comment
plus moins
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.
You should adjust your formula to the reality of your data.

Sheet2 --> B142

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

If you prefer, save your worksheet to some free website, and place the download link here.
It's faster and easier for us to help you.
learningenquirer- Nov 29, 2016 at 05:07 PM
thank you so much, it worked!!!! i forget this is not simply an addition formula
Leave a comment
plus moins
It looks like the dates are consistent enough to just use
in the B column rather than having to use a look up table
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!