Excel, search data in different Sheet2 and put it back on Sheet1 based on their column

%3CLINGO-SUB%20id%3D%22lingo-sub-1798868%22%20slang%3D%22en-US%22%3EExcel%2C%20search%20data%20in%20different%20Sheet2%20and%20put%20it%20back%20on%20Sheet1%20based%20on%20their%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1798868%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20is%20Sheet1%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22zIqku%22%20style%3D%22width%3A%20286px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227968iADC67683E862CE8C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22zIqku%22%20alt%3D%22zIqku%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20Sheet2%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22HO4of%22%20style%3D%22width%3A%20478px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227969iE6C5E151594A467F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22HO4of%22%20alt%3D%22HO4of%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20search%20the%20Fruit%20in%20Sheet2%2C%20identify%20their%20group%2C%20and%20put%20it%20back%20in%20Sheet1%2C%20under%20column%20B%20(Group)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EDesired%20output%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22psMj5%22%20style%3D%22width%3A%20286px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227970i98471162FFA9E226%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22psMj5%22%20alt%3D%22psMj5%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1798868%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1798999%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%2C%20search%20data%20in%20different%20Sheet2%20and%20put%20it%20back%20on%20Sheet1%20based%20on%20their%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1798999%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839021%22%20target%3D%22_blank%22%3E%4011392%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EYou%20may%20use%20this%3A%3C%2FEM%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Rajesh-S_0-1603192944933.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227973i9CEFC2A657089F15%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Rajesh-S_0-1603192944933.png%22%20alt%3D%22Rajesh-S_0-1603192944933.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3EN.B.%26nbsp%3B%3C%2FSTRONG%3E%3C%2FP%3E%3CUL%3E%3CLI%3EFor%20better%20understanding%20I've%20entered%20data%20in%20one%20sheet%20but%20I've%20used%20the%20cell%20references%20reading%20from%20Sheet%202.%3C%2FLI%3E%3CLI%3EEnter%20this%20formula%20in%20cell%20B26%3A%3C%2FLI%3E%3C%2FUL%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(ISBLANK(A26)%2C%22%22%2CIFERROR(INDEX(sheet2!%24D%2425%3A%24G%2425%2CSUMPRODUCT(MAX((Sheet2!%24D%2426%3A%24G%2429%3D%24A26)*(COLUMN(Sheet2!D%2425%3A%24G%2429))))-COLUMN(Sheet2!%24D%2425)%2B1)%2C%22No%20Match%22))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CUL%3E%3CLI%3EAdjust%20cell%20references%20in%20the%20formula%20as%20needed.%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1799002%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%2C%20search%20data%20in%20different%20Sheet2%20and%20put%20it%20back%20on%20Sheet1%20based%20on%20their%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1799002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F839021%22%20target%3D%22_blank%22%3E%4011392%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELet's%20say%20the%20data%20on%20Sheet2%20are%20in%20A1%3AC15%2C%20with%20headers%20in%20A1%3AC1.%3C%2FP%3E%0A%3CP%3EIn%20B2%20on%20Sheet1%2C%20enter%20the%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(INDEX(Sheet2!%24A%241%3A%24C%241%2CSUMPRODUCT((Sheet2!%24A%242%3A%24C%2415%3DA2)*(COLUMN(Sheet2!%24A%242%3A%24C%242)-COLUMN(Sheet2!%24A2)%2B1)))%2C%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThen%20fill%20down.%3C%2FP%3E%0A%3CP%3EI%20recommend%20structuring%20Sheet2%20differently%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S3575.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227974i49AE758C1746856B%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S3575.png%22%20alt%3D%22S3575.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%E2%80%83%3C%2FP%3E%0A%3CP%3EYou%20can%20then%20use%20a%20simple%20VLOOKUP%20formula%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DIFERROR(VLOOKUP(A2%2CSheet2!%24A%242%3A%24B%2436%2C2%2CFALSE)%2C%22-%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAdjust%20the%20range%20as%20needed%2C%20then%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

This is Sheet1

zIqku

 

This is Sheet2
HO4of

 

I would like to search the Fruit in Sheet2, identify their group, and put it back in Sheet1, under column B (Group)

 

Desired output
psMj5

2 Replies
Highlighted

@11392 

 

You may use this:

 

Rajesh-S_0-1603192944933.png

 

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.

 

Highlighted

@11392 

Let's say the data on Sheet2 are in A1:C15, with headers in A1:C1.

In B2 on Sheet1, enter the formula:

 

=IFERROR(INDEX(Sheet2!$A$1:$C$1,SUMPRODUCT((Sheet2!$A$2:$C$15=A2)*(COLUMN(Sheet2!$A$2:$C$2)-COLUMN(Sheet2!$A2)+1))),"-")

 

Then fill down.

I recommend structuring Sheet2 differently:

 

S3575.png

You can then use a simple VLOOKUP formula:

 

=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$36,2,FALSE),"-")

 

Adjust the range as needed, then fill down.