Formula for Primary and Secondary Lookup Tables

[jimbokeep]

I am trying to create a spreadsheet to provide product cost prices from two separate tables of data.

I can use a standard lookup formula to provide a cost price from one table, but if there is no cost price for a specific product in that first table, I'd like the formula to then use a second table to find the result instead.

If there is not a cost price for said product in either table then the formula should return the result "-".

I have attached a very basic example of the tables. Part codes could appear in either the first or second table, but any result available in the first table should supersede the result in the second table.

Hope that all makes sense and could anyone please help?

[Kevin UK]

Hi jimbokeep

Perhaps, something like this!

=IF(ISNA(VLOOKUP(A3,$D$3:$E$11,2,0)),VLOOKUP(A3,$G$3:$H$8,2,0),VLOOKUP(A3,$D$3:$E$11,2,0))

[Fotis1991]

Try in B2 and copy down.

=IFERROR(IFERROR(INDEX($E$3:$E$11,MATCH(A3,$D$3:$D$11,0)),INDEX($H$3:$H$8,MATCH(A3,$G$3:$G$8,0))),"")

[jimbokeep]

Thank you both, I will test these on the actual document when I am back in the office tomorrow and confirm the results.

[Kevin UK]

@ Fotis1991

jimbokeep uploaded a .xls file!

[Fotis1991]

Didn't see it Kevin UK. Thank you for mentioned this.

As you know we can use if(iserror in Excel 2003, but formula will be to long!

[jimbokeep]

Again thank you both for the help with this. The IFERROR solution was the perfect fit for the problem in the end.