Forum Discussion
Solved
if Cell A contains any value in Col 1, Cell B = The header of that Col
Hi Everyone,
Looking for guidance on the following formula.
For E.g.
| Fruits/Seller | Alice | Bob |
| Apple | Durian | |
| Pear | Watermelon | |
| Orange |
If Cell A is Apple or Pear or Orange, Cell B = Alice
Much Appreciated
To achieve this in Excel, you can use the INDEX and MATCH functions. Assuming your data is in columns A, B, and C, and you want the result in cell D1, you can use the following formula:
=IF(ISNUMBER(MATCH(D1, A2:A4, 0)), B1, IF(ISNUMBER(MATCH(D1, B2:B4, 0)), C1, ""))
Here's how this formula works:
- MATCH(D1, A2:A4, 0) checks if the value in cell D1 (e.g., "Apple," "Pear," or "Orange") exists in the range A2:A4 (which contains "Apple," "Pear," and "Orange"). If there's a match, it returns the position of the match; otherwise, it returns an error.
- The ISNUMBER function checks if the result of the first MATCH function is a number (i.e., if there's a match in column A). If it's true, it means the value in D1 matches a value in column A, so it returns the corresponding value in cell B1 (e.g., "Alice").
- If the first MATCH didn't find a match in column A, the formula proceeds to the second MATCH(D1, B2:B4, 0) to check if there's a match in column B. If there's a match, it returns the value in C1 (e.g., "Bob").
- If there's no match in either column A or B, the formula returns an empty string ("").
You can place this formula in cell D1 and adjust the ranges and references as needed to match your actual data.The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
3 Replies
wktay With MS365, Excel 2021 or Excel 2019, the SWITCH function can be used with ISNUMBER & MATCH to return the matching column header. For example, assuming the list for Alice is in column A and Bob is in Column B, you could use:
=SWITCH(TRUE, ISNUMBER(MATCH(D2, $A$2:$A$4, 0)), $A$1, ISNUMBER(MATCH(D2, $B$2:$B$3, 0)), $B$1, "No Match")The first argument of SWITCH is the expression, which in this case is TRUE. Matching values and their corresponding results are entered in pairs (up to 126 pairs in total). A logical test is used here as the value, and the result to be returned is the corresponding column header. If the MATCH function finds the value entered in cell D2, ISNUMBER will return TRUE and SWITCH will return the value in cell A1. If not, ISNUMBER will return FALSE and SWITCH will move on to the next logical test. If none of the subsequent arguments return a match, the final optional argument is used to return "No match".
Note: with MS365 and Excel 2021 only, the XMATCH function can be used instead of MATCH:
=SWITCH(TRUE, ISNUMBER(XMATCH(D2, $A$2:$A$4)), $A$1, ISNUMBER(XMATCH(D2, $B$2:$B$3)), $B$1, "No Match")This method will work fine; however, the formula can become quite long if you have multiple columns to compare against, and it will only return the first match found. For example, if both Alice and Bob sold Bananas, only Alice would be returned. An alternative approach would be to organize your list of Sellers and Fruits in a table as follows:
Seller Fruit Alice Apple Alice Pear Alice Orange Alice Banana Bob Durian Bob Watermelon Bob Banana With this type of layout, the FILTER function can be used with TEXTJOIN to return multiple matches in a single cell. For example:
=TEXTJOIN(", ", TRUE, FILTER($A$2:$A$8, $B$2:$B$8=D2, "No match"))To achieve this in Excel, you can use the INDEX and MATCH functions. Assuming your data is in columns A, B, and C, and you want the result in cell D1, you can use the following formula:
=IF(ISNUMBER(MATCH(D1, A2:A4, 0)), B1, IF(ISNUMBER(MATCH(D1, B2:B4, 0)), C1, ""))
Here's how this formula works:
- MATCH(D1, A2:A4, 0) checks if the value in cell D1 (e.g., "Apple," "Pear," or "Orange") exists in the range A2:A4 (which contains "Apple," "Pear," and "Orange"). If there's a match, it returns the position of the match; otherwise, it returns an error.
- The ISNUMBER function checks if the result of the first MATCH function is a number (i.e., if there's a match in column A). If it's true, it means the value in D1 matches a value in column A, so it returns the corresponding value in cell B1 (e.g., "Alice").
- If the first MATCH didn't find a match in column A, the formula proceeds to the second MATCH(D1, B2:B4, 0) to check if there's a match in column B. If there's a match, it returns the value in C1 (e.g., "Bob").
- If there's no match in either column A or B, the formula returns an empty string ("").
You can place this formula in cell D1 and adjust the ranges and references as needed to match your actual data.The text was created with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and like it!
This will help all forum participants.
NikolinoDE tyvm
