Excel Formula Help
 

I have an issue and not sure of the best formula to use.
I need to do a double look up, i have a list of races and the selections going down the page and need to lookup a runner using the race name as the first lookup then continue down and use the runner number as the second look up, the info i need will be located 5 rows over from this.

I have used a formula like this before.


=INDEX($AA$1:$AT$499,MATCH($BD3,$Z$1:$Z$499,0)+4,9 )

This would only work if i knew where the info was in relation to the race name, hope u understand what i need.

Can you upload a sample worksheet showing how your data is set out Shaun?

Yes, difficult to solve without a sample, but....something like this?


=VLOOKUP(2,INDIRECT("B"&MATCH("Race 2",A:A,0)&":"&"H"&MATCH("Race 2",A:A,1)),7,FALSE)

Where the 2 after the VLOOKUP is the horse number you're looking for and "Race 2" is the name of the race that you're after.

Sorry my bad, the column in blue is the reference, as you see there are individual races and each runner has a reference of 1 or 2 so what i need is to say look up the race then look up the runner reference then return the runner name.

=VLOOKUP(1,INDIRECT("A"&MATCH("VR02",A:A,0)&":"&"E"&MATCH("VR02",A:A,1)),3,FALSE)


Returned "Ella Supero"

Thanks mate, good job.