VLookUp

[AiArch]

I am having a problem with the VLOOKUP function. I keep getting #REF! error message in A! on Sheet1. I am not sure why. Below is the function i am using.

=VLOOKUP(A1,Sheet2!A1:A61,2,0)

Is there a limit to how many values you have have in a lookup function. If so is there a better option (besides a database)?

 

[bluedragon2]

what kind of data are you lookinng up?

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AiArch]

The values look like this CH01, CH02, TB01. Then it returns a value like for CH01 - Chair 1, CH02 - Chair 2, TB01 - Table 1. I tried the regular LookUp Function and it seems to work only if the values are in Ascending order. This is the functino for that

=LOOKUP(E18,Sheet2!A2:A65,Sheet2!B2:B65)

Is there a way that it would work if they are not in Ascending order?

 

[bluedragon2]

You need to reference your entire table in the vlookup, try:

=VLOOKUP(A1,Sheet2!A1:B61,2,0)

[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[AiArch]

I used that at first but I keep getting #REF! error message in A1 on Sheet1. I am stumped why. I have never had this propblem before.

 

[DirkStruan]

Your first post says

=VLOOKUP(A1,Sheet2!A1:A61,2,0)

Meaning you are trying to return the second column entry in a single column range, hence the #REF!

Blue's answer refers to both columns A & B on Sheet2.

Dirk

Everything useful perishes through use....Should I not rejoice at being useless?

 

[AiArch]

Sorry about that. Thanks for the help.