Vlookup (if duplicate values in list)

[burt0na]

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

 

[Robert Mika]

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]

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]

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]

NIce one Stefano.
I can not bit that.
Just I would wrap it in an ISNA function.