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

"matt_the_brum" <[email protected]> wrote in message
news:[email protected]...
|
| 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...fo&userid=5751
| View this thread: http://www.excelforum.com/showthread...hreadid=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 wrote:
> 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.
>
>
> --
> matt_the_brum
> ------------------------------------------------------------------------
> matt_the_brum's Profile: http://www.excelforum.com/member.php...fo&userid=5751
> View this thread: http://www.excelforum.com/showthread...hreadid=568240

[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.