In Sheet 2, I am looking to search Sheet 1 for an instance of a value, that could be in one of four columns.
COLUMN A | COLUMN B | COLUMN C | COLUMN D | COLUMN E |
ABS 123 | March | 26444 | | 43454 |
ABS 456 | June | 87834 | 45003 | |
ABS 789 | August | | | |
ABS 000 | October | | | 36543 |
There are four order numbers in Sheet 1 (ABS 123, ABS 456, etc.) in Column A. Column is the month the order was processed. Columns C to E represent different phases in processing. There may be a processing number in one of the columns.
In Sheet 2, I'd like to find instances of the order number and processing number. For example, if I "search" for ABS 123 and 26444, it would report "March", because it searched Column A and Columns C to E for both the order number in Column A (ABS 123) and the processing number in Columns C, D, and E (26444), and if found, displays the month in Column B (March). Otherwise, it displays "N/A".
COLUMN A | COLUMN B | COLUMN C
ABC 012 | 23000 | N/A
ABC 123 | 26444 | March
ABC 123 | 32322 | N/A
ABC 123 | 43454 | March
ABC 331 | 42232 | N/A
ABC 456 | 45003 | June
ABC 456 | 87834 | June
ABC 789 | 12345 | N/A
ABC 000 | 36543 | October
Sheet 2 already contains information in Columns A and B. The formula appears in Column C, which references Sheet 1 and looks for the value in Column A from Sheet 2 in Column A of Sheet 1 and Column B from Sheet 2 in Columns C to E of Sheet 1 and either displays the value of Column B of Sheet 2 or N/A (not found).
I get how to use the INDEX/MATCH function, but not sure how to have it look for a value (Column B in Sheet 2) in multiple columns (C, D, and E) in Sheet 1.
Thanks for your guidance!