Simple query

[krisbuddies]

Hi guys

This one is a 2 min job for you guys!! But I'm really struggling to figure it out...

The data is a table of dates and percentages, and I want the formula to tell me the percentage when I tell it the date.


So, and apologies if this isn't clear, but I want the formula to read "if b1 = one of j2:t2, then b3:b9 fills as j3:j9 - t3:t9"

b1 is the trigger date, j2:aa2 is the dates, formula is in b3 and fills from j3:aa3.



Does that make any sense?? I think I need INDEX / MATCH but am struggling to make it work.

Thanks in advance for your help

 

[Yongle]

Try this

In B3 copy down
=INDEX($J$1:$AA$9,ROW(),MATCH($B$1,$J$2:$AA$2,0))

[TABLE="width: 686"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"]19/07/18
[/TD]
[TD]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]21/07/18[/TD]
[TD="align: right"]20/07/18[/TD]
[TD="align: right"]19/07/18
[/TD]
[TD="align: right"]18/07/18
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]300
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]300
[/TD]
[TD="align: right"]400[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]301[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]101[/TD]
[TD="align: right"]201[/TD]
[TD="align: right"]301
[/TD]
[TD="align: right"]401[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]302[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]102[/TD]
[TD="align: right"]202[/TD]
[TD="align: right"]302
[/TD]
[TD="align: right"]402[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]303[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]103[/TD]
[TD="align: right"]203[/TD]
[TD="align: right"]303[/TD]
[TD="align: right"]403[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]304[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]104[/TD]
[TD="align: right"]204[/TD]
[TD="align: right"]304
[/TD]
[TD="align: right"]404[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]305[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]105[/TD]
[TD="align: right"]205[/TD]
[TD="align: right"]305
[/TD]
[TD="align: right"]405[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]306[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]106[/TD]
[TD="align: right"]206[/TD]
[TD="align: right"]306
[/TD]
[TD="align: right"]406[/TD]
[/TR]
</tbody>[/TABLE]

 

[krisbuddies]

Thank you so much Yongle, works perfectly

 

[Yongle]

thanks for the feedback

 

[krisbuddies]

So Yongle, hope you don't mind but can you help me with something else please?

I presume you can match to 2 different criteria? So on that example, I'm matching to $b$1, what if I want to match to 2 sets of data?


so, again, in laymans terms (!):
data is
a3:a9, 3:9 - b3:b9, %ages
d3:d9, %ages - e3:e9, %ages


I've got manual inputs at a16 (which will be one of the range a3:a9) and at d16 (which will be one of the range d3:d9) - the formula in g16 = the sum of the result of b3:b9 and the result of e3:e9!!

Wow, that makes it sound so complicated but it really isn't... I just can't manipulate Excel as I'd like. I think this is another index match query but I can't get my head around it!!!

Thanks again, really appreciate your help (and the fact that you can translate my ramblings!!!