Using nesting vlookup formulas for use in automation code

0
votes

I am trying to write a formula that uses different vlookups depending on whether the formulas produce errors or not. I have 3 tabs, each with 3 columns.the first two columns contain values that are in the vlookup, with the third colum containing the value I want to find. Basically i want a formula that will use different vlookups depending on whether the lookup value is found in teh first tab or the second . Below is the formula i am working with. It works to bring in values from Sheet1 into Sheet3, but doesnt work when the values are in Sheet2. Also will be inserting this formula into vba code for automation use.

=IF(OR(ISERROR(VLOOKUP(B2,Sheet1!B2:C19,2,FALSE)),ISBLANK(VLOOKUP(Sheet3!B2,Sheet1!B1:C19,2,FALSE))),IF(OR(ISERROR(VLOOKUP(Sheet3!A2,Sheet1!A1:C19,3,FALSE)),ISBLANK(VLOOKUP(Sheet3!A2,Sheet1!A1:C19,3,FALSE))),IF(OR(ISERROR(VLOOKUP(Sheet3!B2,Sheet2!B1:C21,2,FALSE)),ISBLANK(VLOOKUP(Sheet3!B2,Sheet2!B1:C21,2,FALSE))),IF(OR((ISERROR(VLOOKUP(Sheet3!A2,Sheet2!A1:C21,3,FALSE))),ISBLANK(VLOOKUP(Sheet3!A2,Sheet2!A1:C21,3,FALSE))),"non",VLOOKUP(Sheet3!A2,Sheet2!A1:C21,3,FALSE)),VLOOKUP(Sheet3!B2,Sheet2!B1:C21,2,FALSE)),VLOOKUP(Sheet3!A2,Sheet1!A1:C19,3,FALSE)),VLOOKUP(Sheet3!B2,Sheet1!B1:C19,2,FALSE))

it returns the value sI am looking for up until the lookup values start occuring in Sheet2. At that point it shows me "non".

Any help would be greatly appreciated! Thanks

1
excelnestedvlookupvba

1 Answers

0
votes

Your first VLOOKUP does not reference "Sheet3".

I think The below equation does what you are looking to do (assumes you are on Sheet3).

=IF(IFERROR(VLOOKUP($B$1,Sheet1!A:C,3,FALSE),"")="",IF(IFERROR(VLOOKUP($B$1,Sheet1!B:C,2,FALSE),"")="",IF(IFERROR(VLOOKUP($B$2,Sheet2!B:C,2,FALSE),"")="",IF(IFERROR(VLOOKUP($B$2,Sheet2!A:C,3,FALSE),"")="","nan",VLOOKUP($B$2,Sheet2!A:C,3,FALSE)),VLOOKUP($B$2,Sheet2!B:C,2,FALSE)),VLOOKUP($B$1,Sheet1!B:C,2,FALSE)),VLOOKUP($B$1,Sheet1!A:C,3,FALSE))