Vlookup function imbedded in the table_array value of a vlookup function

Hi,

I am creating a go-karting championship management system for my a-level and I am having a few problems.

Basically there are 6 races and They are set out as Race1,2,3,4,5,6 and the data for the drivers is entered under that. However, the spreadsheet is organised via the dates of the races which means that the data entered as race 1 could be for race 2,3,4,5.

I have already got it to calculate this and it displays the date, the original race number and the race number after organisation from the dates of the races.

Now, this is all hidden from the user in a hidden sheet and on the user viewed side I want the races to be viewed by date position and not by their original name/position.

To do this I want to use a Vlookup function which looks up b34 within the table array value which will change, relative to the dates of the races.

It's probably best to give you my formula and see if you can see anyways around it:


=VLOOKUP(B34,VLOOKUP(1,C122:D127,2,False),2,False)

when I use this, in theory it should work and give a correct value but instead it just gives a #value error because I think it is looking up "VLOOKUP(1,C122:D127,2,False)" as the name of a table array but I want it to lookup the result of this formula as the table array name.

just to confirm:

=VLOOKUP(1,C122:D127,2,False) on it's own gives "races_1" which is the name of one of my table arrays.

and

=VLOOKUP(B34,races_1,2,False)



I'm sorry if I've confused you but any help would be greatly appreciated

Thanks
Scott

Try using the INDIRECT( ) function - look it up in Help.

Pete