CODEWITHSUNDEEP
excelexcel-formula
Codash
Codash

Reputation: 99

Excel IF/THEN/VLOOKUP Nested Formula

I have two tabs in an excel file. Sheet 1 and Sheet 2. I have been working on a nested IF/THEN/OR/AND formula in excel that needs to do the following:

In Sheet 1, I need to make sure that two columns - Name 1 and Name 2 - match. If they do not match, I need to make sure that Name 2 matches to a Name column in Sheet 2.

Formula must - If Name 1 and Name 2 in Sheet 1 match, then YES, else NO. If NO, then VLOOKUP Name from Sheet 2 and Match with Name 2 in Sheet 1. If there is a match then display YES, else NO.

So far I have two separate columns that check for this. The first uses an exact statement to match the two name columns in sheet 1. The second does a vlookup to see if the name appears in the second sheet. I need this in one cell formula if possible and I am not sure how to do so without splitting.

+----------+----------+--+--+----------+
| Sheet 1  |          |  |  | Sheet 2  |
+----------+----------+--+--+----------+
| Column 1 | Column 2 |  |  | Column 1 |
| Name 1   | Name 2   |  |  | Name     |
+----------+----------+--+--+----------+

Upvotes: 0

Views: 254

Answers (1)

Glenn G
Glenn G

Reputation: 667

You will need to adjust the formula below for your sheet names and ranges, but this is a formula that will check column A against column B and if not the same, will check column B against a vlookup table. It also lets you know if there was not a match found in the vlookup table instead of just giving the error code #N/A

=IF(A3=B3,"Columns Match",IFERROR(VLOOKUP(B3,Sheet2!A4:B14,2,FALSE),"No Match Found In Vlookup"))

Upvotes: 0

Related Questions