2

In Sheet 2, I am looking to search Sheet 1 for an instance of a value, that could be in one of four columns.

Sheet 1

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". 

Sheet 2

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!

Functionality

Comments

Try something like this...

 

=INDEX({Month}, MATCH([Column A]@row, {Column A}, 0), IF(MOD(MATCH([Column B]@row, {Columns C - E}, 0), 3) = 0, 3, MOD(MATCH([Column B]@row, {Columns C - E}, 0), 3)))

 

EDIT:

Please note: The divisor within the MOD function would be however many columns you are using in your range. The number in the "if_true" section of the IF statement will also be the same as the number of columns you have in your range.

Please disregard the above solution. It is not what you are looking for. I will do some more testing and see what I can come up with.

 

My apologies.