Variable Table Array in Lookup Function

[matt_the_brum]

Not sure if that title makes sense but...

I'm using a formula

=HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

At the moment it is looking for values in a table on Labour!

I have similar tables (in the same place ie B1:L2) on other
worksheets.

I want to chose which worksheet it looks at by creating a Drop Down
list in another cell on the same worksheet.

So basically I want the red bit in

HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

to be dependant on what is in another cell.

 

[Niek Otten]

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Not sure if that title makes sense but...
|
| I'm using a formula
|
| =HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)
|
| At the moment it is looking for values in a table on Labour!
|
| I have similar tables (in the same place ie B1:L2) on other
| worksheets.
|
| I want to chose which worksheet it looks at by creating a Drop Down
| list in another cell on the same worksheet.
|
| So basically I want the red bit in
|
| HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)
|
| to be dependant on what is in another cell.
|
|
| --
| matt_the_brum
| ------------------------------------------------------------------------
| matt_the_brum's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5751
| View this thread: http://www.excelforum.com/showthread.php?threadid=568240
|

 

[matt_the_brum]

Thanks. Had a look but not having much luck with the INDIRECT
function.

All I want is the Red writing in

=HLOOKUP(A10,Labour!$B$1:$L$2,2,FALSE),FALSE)

to equal whatever is in say cell A3.

 

[spxer]

I am trying somethig similar with VLOOKUP. If you get it working please
post the solution. Thanks

 

[matt_the_brum]

Likewise Spxer. Won't be working on it until next week now but will
post any progress.

 

[Pete_UK]

Matt, try this:

=HLOOKUP(A10,INDIRECT(A3&"!$B$1:$L$2"),2,FALSE),FALSE)

where A3 contains the sheet name. Ensure there are no spaces in any of
your sheet names, otherwise you will have to include apostrophes around
them in the formula.

Hope this helps.

Pete

 

[matt_the_brum]

Thanks Pete. Its working in the small formula, just got to try and fit
it into this,

=IF(ISNA(VLOOKUP(C14,Labour!$A$3:$L$12,HLOOKUP(A14,Labour!$B$1:$L$2,2,FALSE),FALSE)),,VLOOKUP(C14,Labour!$A$3:$L$12,HLOOKUP(A14,Labour!$B$1:$L$2,2,FALSE),FALSE))

but it will have to wait until Monday now.