Vlookup (if duplicate values in list)

burt0na

New Member
Joined
Aug 23, 2011
Messages
27
Hi,

I am comfortable doing a normal vlookup; the spreadsheet i use pulls info from two different systems which pulls the invoice number through which the vlookup searches for and then pulls through a voucher number.

However i have encountered a problem whereby information or data i pull from on sometimes pulls through two values the same in the lookup range. (Maybe is a duplicate invoice has been entered by mistake)

If this happens the first entry pairs off with a system voucher that is created and pulls through the voucher number the second entry has nothing to pair with so pulls through a zero value.

This causes a problem if the zero value appears higher up the list than the correct voucher number as the vlookup finds the zero value first when actually i need it to look further down the list to get the correct voucher number.

The spreadhseet is 10,000 lines+ long so manual checking isnt an option, can a vlookup be customised to skip any zero values and look for the next value? Or something similar

Thanks,
Andy
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
Are you invoice entry just numbers or can be numbers mixed with letters?
Is it possible to have more than one duplicate?
Sample of your data woudl be useful.
 

Canapone

Active Member
Joined
May 10, 2007
Messages
463
Hi, a first answer

in A2 the number of invoice, in column Z the number you need

=INDEX(Z2:Z1000,MATCH(1,INDEX(1/(Z2:Z1000<>0)*(U2:U1000=A2),),0))

Hope it helps


Edit: Hi, Robert
 

burt0na

New Member
Joined
Aug 23, 2011
Messages
27
Invoices are just numbers

It is possible but unlikely as that would mean it was duplicated twice in error.

One spreadsheet pulls through all the data from external system. Second spreadsheet looks at 1st and returns voucher numbers using the invoice numbers as lookup ref

Thanks,
Andy
 

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
NIce one Stefano.
I can not bit that.
Just I would wrap it in an ISNA function.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,906
Messages
5,598,783
Members
414,259
Latest member
beetle12

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