Vlookup question

JimH

Board Regular
Joined
Apr 21, 2002
Messages
94
Office Version
  1. 2010
Platform
  1. Windows
Will vlookup work when there are duplicate entries in both spreadsheets? For example, in the spreadsheet where the vlookup formula is located, I go to the part number column to lookup the part number. In this column there are multiple entries of the same part number. I then go to a second spreadsheet to find that same part number (also having multiple entries of the same part number) and find the corresponding price to put in a column in the spreadsheet where the vlookup formula is.

When I spot check the #N/As, I find that the part number exists in the spreadsheet I'm doing the lookup in.

I'm sure the multiple entries are causing the problem, but I don't know how to get around the problem.

Any help would be appreciated.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Multiple entries make no difference to VLOOKUPS however, they only return the first instance (so if you want to find the second instance etc you will have to use a more refined tool).

Are you using syntax = VLOOKUP(criteria,range,col_index,False)?

If you are check that the part numbers are IDENTICAL - in terms of character length. Take the cell you're looking up (say A1) and test it's length by = LEN(A1). Do the same thing on the matching part number in the lookup range. You may find that there's an additional space or something in one of the cells which means VLOOKUP will not find a match.

If it's still not working and all the above is aok then drop another message
 

Forum statistics

Threads
1,144,734
Messages
5,725,983
Members
422,652
Latest member
Elnene1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top