How to use HLOOKUP for different sheets depending on results from 2 cells

[james.ositis]

I have 2 cells with 3 possiable results each. Depending on the results
from both these cells, determines which sheet needs to be used to
lookup the correct data on. I can get it to work when I specify what
sheet to use, but the sheet is a variable and I cannot figure out how
to get it to work. Thank

 

[T. Valko]

How about providing some details!

I have 2 cells with 3 possiable results each.

Which 2 cells? What are the 3 possible results?

What sheet do you want to use in the lookup based on which result?

What does your formula look like right now?

Biff

 

[james.ositis]

=HLOOKUP(D14, TROOP0!A1:R51, MATCH(F14, TROOP0!A2:A51, 0), TRUE)

The problem is that TROOP0 could be TROOP0, TROOP1, TROOP2, UNARMORED0,
UNARMORED1, UNARMORED2, OR ARMORED0, ARMORED1, ARMORED2.

This depends on what is chosen in 2 previous cells c14 and e14. In c13
the option is given to chose troop, armored, unarmored, and in e14 the
option is given for 0, 1, 2

The sheets are numbered TROOP0, TROOP1 TROOP2 etc, all with varing data
on them.

I hope I'm being clear enough for you to get a mental picture

thanks

 

[T. Valko]

I'm assuming the lookup tables on each sheet are in the exact same location.

=HLOOKUP(D14, INDIRECT(C14&E14&"!A1:R51"),
MATCH(F14,INDIRECT(C14&E14&"!A2:A51"), 0), TRUE)

Biff

 

[james.ositis]

Well by entering that now I get a #REF! error. But I have data is both
the cells. Not sure what is going on with it

 

[tankervet]

I figure I should try to give more info. This is an example of the
data in the varying sheets. This sheet being TROOP 0. But the data is
different between all 9 sheets but its in this same format

200 300 400 500 600
1 100 100 100 100 100
2 100 100 100 100 100
3 100 100 100 100 100
4 100 100 100 100 100
5 100 100 100 100 100
6 100 100 100 100 100
7 100 100 100 100 100
8 100 100 100 100 100
9 96 97 100 100 100

Thanks again

 

[T. Valko]

The sheets are numbered TROOP0, TROOP1 TROOP2 etc

This sheet being TROOP 0

So, do the sheet names have spaces in them?

If so, try this:

=HLOOKUP(D14, INDIRECT("'"&C14&" "&E14&"'!A1:R51"),
MATCH(F14,INDIRECT("'"&C14&" "&E14&"'!A2:A51"), 0))

Biff

 

[tankervet]

I have tried it both ways with space and without and its the same. For
some reason it give me the REF error on "indirect" in the formula.

 

[T. Valko]

Can you send me a copy of this file? I see you have a military email
address. I'm an AF vet. If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff

 

[tankervet]

File sent and help greatly appreciated!

Can you send me a copy of this file? I see you have a military email
address. I'm an AF vet. If so, I'm at:

xl can help at comcast period net

Remove "can" and change the obvious.

Biff