VLOOKUP not giving me the right results! 4

[jfussell]

My VLOOKUP function seems to be giving me the results from the prior row. Here's what I have...

4 columns:
Column A is my abbreviated states lookup value
Column B is where I want my results displayed
Columns C&D are my table array where I want the results to pull from column C

So, here's my formula:
=VLOOKUP(A1,$C$4:$D$56,1)

However it appears to be showing the results of the prior row instead of the correct row. So, A1 would be FL, B1 should show "Florida" but it's showing "Delaware". Also for the table array I have it sorted by column D. Any thoughts/suggestions would be greatly appreciated! Thanks.

 

[xlhelp]

In this case VLookup isn't going to work for you. The range after A1 needs to include the known value column.

Please check out Match and Index functions in Excel help

Member- AAAA Association Against Acronym Abusers

 

[xlhelp]

I think may be I misread your question. Does C4 have Delaware in it? Why does your table start at C4?

Member- AAAA Association Against Acronym Abusers

 

[bluedragon2]

Sort it on Column C...

[Blue]Blue[/Blue] [Dragon]

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

 

[bluedragon2]

nevermind, what is in A1 and how is your table setup?

[Blue]Blue[/Blue] [Dragon]

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

 

[AnotherHiggins]

From what you're describing, it sounds like your data looks something like this:
[tt]
A B C D
NC __ NC North Carolina
FL __ FL Florida[/tt]

Is that right? If not, please provide a bit of sample data.

Change the last argument to false, or "0".

[tab][COLOR=blue white]=VLOOKUP(A1,$C$4:$D$56,[red]0[/red])[/color]

That tells Excel to only find an exact match.

Leaving the final argument blank (or TRUE or 1) is used to have Excel return the results from an approximate match. That is, the value that is the next largest value that is less than lookup_value. For this to work properly, the list must be sorted on the first column as bluedragon2 suggested.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[jfussell]

OK let me try and answer everyone's questions in this post...

John, my columns look like this...

A B C D
FL Delaware Alaska AK
MN Mississippi Alabama AL
HI Hawaii Arkansas AR
IL Hawaii Arizona AZ etc

A = lookup value
B = VLOOKUP formula for results
C&D = table array

So, as you can see my VLOOKUP lies in column B but it's not giving the right results; it should be Florida, Minnesota, Hawaii and Illinois respectively. And if I use =VLOOKUP(A1,$C$1:$D$53,0) it gives me #VALUE!. And even when I sort it by column C instead of D, then the results are District of Columbia, Missouri, Hawaii and Idaho.

I think I've answered all the questions above to help solve this mystery. Thanks again!

 

[AnotherHiggins]

When using VLookup, the value your are looking for (state abbreviations in this case) [!]MUST[/!] be the first column in the table.

Cut column D, right click on column C and select Insert Cut Cells. Now use the formula I have posted above (with 0 for final argument).

Voila!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[bluedragon2]

Invert the table, ie put the 2 letter in col C and the full name in col D and sort on col C...

Change VLOOKUP with a 2 instead of a 1.

What you are doing now is looking up in col C with the 2-letter and getting the nearest match.



[Blue]Blue[/Blue] [Dragon]

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

 

[xlhelp]

Now it makes sense, try
=INDEX(C1:C56,MATCH(A1,D156,0),0)

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

I just realized my previously posted formula had an argument missing. Once you restructure your data, this will work:

[tab][COLOR=blue white]=VLOOKUP(A2,$C$4:$D$56,2,0)[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[yogia]

Hi jfussell:

In addition to the fine solutions you have already received, you may also use the following formula in cell B1 ...

=LOOKUP(1,1/(A1=$D$1:$D$55),$C$1:$C$55)

and copy it down.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC

http://www.energyefficientbuild.com

 

[jfussell]

WOW! Thank you everyone so very much!! I ended up just swaping the columns and adjusted the formula and now it works like a champ!! Thanks again for all your wonderful instructions!