Using and INDEX and MATCH in Vlookup for col_index_num??

[tompee29]

Hi,

In the simple spreadsheet attached i have a vlookup looking 4 columns across to pull the data for the 25/10/14.
Instead of having the number 4 there is it possible to replace it with a Index and match function so say i type the date 26/10/14 in A1 it will pull the result of 8 back??

If so how?!?

Many thanks in advance!
Tom.

EDIT:
I dont think i was very clear in the first instance. I have attached a new file (test 3). What i am looking to be able to do is change the date in A1 to pull in the data for that date in the summary below using a lookup table. Thanks.

[TPDave]

Hi There,

Your sheet isn't saying what your message is. What cell do you want the formula in, what reference do you want it to look up and what output do you want the formula to display?

The index and match is easy, I just need to know where the data is relative to everything else on the sheet. Thanks

[judgeh59]

does this work for you?

=+VLOOKUP(D11,D6:M6,HLOOKUP(A1,E4:M6,3),FALSE)

[tompee29]

hmmm, i like that but it doesnt work

[tompee29]

Hiya.

sorry if i wasnt clear...

so the formula in F11, what would be in place of the 4 if i wanted to index and match it to the date instead based on a date in A1?? Does that make sense?

so if i put the date 25/10/14 in A1 it would return the value 4 in F11. If i put the date of 26/10/14 in A1 it would return 8 in cell F11.

Hope that makes sense....

Apologies if not.

Cheers,
Tom.

[tompee29]

It only works if you enter 25/10/14, if you enter any other date it gives random answers or #REF!

[TPDave]

Aah, gotcha!

=INDEX(E6:M6,1,MATCH(A1,E4:M4,0))

Try that

[tompee29]

ahaa... perfect, your a legend, thank you!!!

[TPDave]

Hmm, it works OK in the attached file? Have a play and let me know.

Thanks

[tompee29]

no, sorry, that was a reply to judgeh59, sorry... not sure what i am doing on this... yours worked, thanks again.

[tompee29]

Hmm, it works OK in the attached file? Have a play and let me know.

Thanks

In fact it doesnt do what i need it to do It needs to reference the 10 in D11 because i am going to be looking the 10 up in a much larger range not just D6:M6.

So needs to be a match and index inside a lookup or i guess a hlookup inside a vlookup?

[judgeh59]

sorry just a bit confused....the HLOOKUP version worked?

[tompee29]

sorry, no didnt work?