Vlookup function imbedded in the table_array value of a vlookup function

[snewham]

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,C122127,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,C122127,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,C122127,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

 

[Pete]

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

Pete