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
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