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))

	19/07/18
									21/07/18	20/07/18	19/07/18	18/07/18
	300								100	200	300	400
	301								101	201	301	401
	302								102	202	302	402
	303								103	203	303	403
	304								104	204	304	404
	305								105	205	305	405
	306								106	206	306	406

<tbody>
</tbody>

 

[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!!!