Nested If Statements with Two Vlookups

[rrmando18]

Happy almost Friday everyone. I need help. I can't get this to work. It returns either "Lump Sum" or #N/A.

If A18 is found in 'Lump Sum' sheet then return "Lump Sum"

If A18 is found in 'Material' sheet then return "Material"

If A18 is not found in either of those two sheets then return "TS Labor"

=IF(VLOOKUP(A18,'Lump Sum'!B:B,1,FALSE),"Lump Sum",IF(VLOOKUP(A18,Material!B:B,1,FALSE),"Material","TS Labor"))

Thank you!

 

[dreid1011]

When your first VLOOKUP doesn't find the value, it returns #NA! and that ends up voiding the rest of the formula. You could wrap each VLOOKUP in something like this:

ISNA( VLOOKUP() )= FALSE

or replace with XLOOKUP and return a 0 if the value is not found.

 

[Arts]

Would the below give you what you want?

=IF(ISNA(VLOOKUP(A18,'Lump Sum'!B:B,1,FALSE)),IF(ISNA(VLOOKUP(A18,Material!B:B,1,FALSE)),"TS Labor","Material"),"Lump Sum")

 

[rrmando18]

Would the below give you what you want?

=IF(ISNA(VLOOKUP(A18,'Lump Sum'!B:B,1,FALSE)),IF(ISNA(VLOOKUP(A18,Material!B:B,1,FALSE)),"TS Labor","Material"),"Lump Sum")

Yes, thank you. I was trying to get the ISNA or Xlookup to work but wasn't having any luck. Thank you both.

 

[Arts]

No problems, glad it helped