[SOLVED]Variable Named Lookup Table?

[splenguin]

Hey gang-

Say I have a number of named ranges, which are different lookup tables. Then I have two named cells, one which does some magic to determine the lookup_value for a VLOOKUP, and one which does some magic to determine the table_array [which of the named ranges]. I'm having trouble using the value of one of the "magic" cells as the lookup_value in the VLOOKUP formula. I was able to use INDIRECT for the table_array part of the formula, but it doesn't seem to be working for the lookup_value part.

EXAMPLE
I have NamedRangeA, and NamedRangeB [both lookup tables].
I have MagicCellA, which determines that the value to be looked up is 5555.
I have MagicCellB, which determines that the named range in which to lookup the value NamedRangeB.

My formula:
=VLOOKUP(MagicCellA,INDIRECT(MagicCellB),2,FALSE)
results in a Value Not Available error.

I know that the table_array part is working, because the formula:
=VLOOKUP(5555,INDIRECT(MagicCellB),2,FALSE)
returns the correct value. All I've done is put in 5555 manually, rather than using MagicCellA.

I think it's looking for the text MagicCellA, rather than the value of MagicCellA, or something like that - which is why I used INDIRECT on the table_array part. Yet the formula:
=VLOOKUP(INDIRECT(MagicCellA),INDIRECT(MagicCellB),2,FALSE)
doesn't work either.

Can anyone see what I'm doing wrong? Thanks very much in advance,
joe

[NBVC]

Possibly the format of MagicCellA is text....

Go to that cell and change the format to General or Number... click Ok.. then hit F2 on that cell and Enter.

Does =VLOOKUP(MagicCellA,INDIRECT(MagicCellB),2,FALSE) work now?

[splenguin]

I'm afraid not; it was already in General format, though I did try Number format as well - no luck!

[Haseeb Avarakkan]

Hello splenguin,

Add +0 to MagicCellA, Try this versions

=VLOOKUP(MagicCellA+0,INDIRECT(MagicCellB),2,FALSE)

Does this work? If not can you please attach your dummy file?

[splenguin]

It worked! And I have no idea why! What does adding +0 to the name do?

Thanks very much for helping with my problem.

[Haseeb Avarakkan]

+0 will convert to actual numbers, If numbers are stored as text. like cell formatted as text or numbers entered with a apostrophe.

MagicCellA, is stored as text. Either cell format should be text or entered with a apostrophe. If you add +0 will convert it to an actual number like 5555.

[NBVC]

If you had followed the instructions I gave, you would have also gotten the result.

[splenguin]

As I mentioned in my previous response, I did follow your instructions, but the format was already set to General. Not sure why this worked and that didn't, but thank you both for your responses.