Forum Discussion
Excel, search data in different Sheet2 and put it back on Sheet1 based on their column
This is Sheet1 This is Sheet2 I would like to search the Fruit in Sheet2, identify their group, and put it back in Sheet1, under column B (Group) Desired output
You may use this:
N.B.
- For better understanding I've entered data in one sheet but I've used the cell references reading from Sheet 2.
- Enter this formula in cell B26:
=IF(ISBLANK(A26),"",IFERROR(INDEX(sheet2!$D$25:$G$25,SUMPRODUCT(MAX((Sheet2!$D$26:$G$29=$A26)*(COLUMN(Sheet2!D$25:$G$29))))-COLUMN(Sheet2!$D$25)+1),"No Match"))
- Adjust cell references in the formula as needed.