VLOOKUP not working correctly

[robholmes]

I am using VLOOKUP to compare two cells and if the cells are equal then it places the information in the table. If it doesn't match it should display 'N/A' but instead it just continues to list the incorrect information. It starts off right and then gets off track.![enter image description here][1]

excel.PNG


If you see here column n starts with the correct match and then gets off track. The function I have for column N is **=VLOOKUP(R1,Accounts,1,FALSE)**

What /Is there a fix for this issue?

[mikeTRON]

Can you post a sample workbook instead of a photo?

[nigelbloomy]

It looks like this may be what you want.

Please Login or Register  to view this content.

[robholmes]

That worked! Can you explain what my problem was?

[nigelbloomy]

What you wanted was to see if the value in column A matched anything in column R. Your formula looked in column R to see if there was a match in column A (Which there always was because R had the red items from column A). You just had it backwards.

[robholmes]

Thanks so much! So if I wanted to compare R to A again but move the S column to the note column how could I do that?

[nigelbloomy]

Please Login or Register  to view this content.

That will give you the note from column S which is probably more useful. You can even enclose it with an IFERROR formula so it doesn't give you the ugly #N/A errors.

Please Login or Register  to view this content.

[robholmes]

Wow, great. Last question, how would I compare what is in column O with what is in column W and if the same move what is in Column X into column P

Capture.PNG

[robholmes]

Also is there a way to hide and lock columns so if I send this file they cant see columns R thru W?

[nigelbloomy]

It looks like you already have column N matched with column R and column O matched with column S. So in P2 you could put

Please Login or Register  to view this content.

This will lokup the short note in column O and give you the long description for it from column X.

You can hide column R through X by selecting them, right click, hide.

[robholmes]

Sheet 1snap1.PNG Sheet3 snap2.PNG
I need to compare Pat num in column C with account Number on C in sheet 3 and if they are a match I need to take the corresponding DBNUM from B on Sheet3 and place all of this info in cell AM. How would I do that?

[nigelbloomy]

Because Vlookup can only look to the right (it's not an ambi-turner) and on sheet 3 your DBNUM is on the left of the account number, you can use INDEX MATCH instead. If you put this in AM2, it should look at the value in C2, the pat num, and search for it on sheet 3 in column C, account number. If it finds it it will tell index what row to look at in column B and give you that DBNUM. If it doesn't find anything it will just give you a blank.

Please Login or Register  to view this content.