One formula with three different criteria

[itsgrady]

Need 1 formula based on three different criteria. Spreadsheet-1 has a table with 3 columns of data (lookup array – Column B and C). Spreadsheet-2 has a table with 3 column of information (return array – Column B and C). Columns A on both spreadsheet has the matching data (the lookup value). Column C on spreadsheet-1 will be the column for the formula.

If Columns A on both spreadsheet matches and column B on spreadsheet-1 has data, return column B on spreadsheet-2 to column C on spreadsheet-1.

If Columns A on both spreadsheet matches and column B on spreadsheet-1 is empty, return column C on spreadsheet-2 to column C on spreadsheet-1.

If Columns A on both spreadsheet matches and column B on spreadsheet-1 has “INV01”, return “Dan Mann” to column C on spreadsheet-1.

I tried this and I also wanted to reference table and not the column is possible:

=IF(ISBLANK(B1), VLOOKUP(A1, 'Spreadsheet-2'!$A$1:$C$100, 3, FALSE), IF(B1="INV01", "Dan Mann", VLOOKUP(A1, 'Spreadsheet-2'!$A$1:$C$100, 2, FALSE)))

I only have access to my phone. I hope the paragraphs are understandable. Thanks for any help.

 

[dreid1011]

Perhaps this:

=IF(B1="INV01","Dan Mann",XLOOKUP(A1,'Spreadsheet-2'!$A$1:$A$100,IF(B1<>"",'Spreadsheet-2'!$B$1:$B$100,'Spreadsheet-2'!$C$1:$C$100)))

 

[itsgrady]

Perhaps this:

=IF(B1="INV01","Dan Mann",XLOOKUP(A1,'Spreadsheet-2'!$A$1:$A$100,IF(B1<>"",'Spreadsheet-2'!$B$1:$B$100,'Spreadsheet-2'!$C$1:$C$100)))

 

[itsgrady]

Thanks for the help. Your formula with Xlookup works a lot better.thanks for taking time to help me.

 

[dreid1011]

Thanks for the help. Your formula with Xlookup works a lot better.thanks for taking time to help me.

You're welcome, thanks for the feedback.