SOLVED

Help with INDEX MATCH VLOOKUP functions

%3CLINGO-SUB%20id%3D%22lingo-sub-1485307%22%20slang%3D%22en-US%22%3EHelp%20with%20INDEX%20MATCH%20VLOOKUP%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485307%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%20I'm%20new%20to%20Excel%20and%20looking%20for%20guidance.%20Sorry%20if%20I'm%20wasting%20your%20time.%20I%20need%20to%20match%20the%20data%20in%20Sheet2%20to%20Sheet1%20so%20that%20it%20marks%20an%20%22x%22%20in%20the%20box%20for%20the%20corresponding%20plant%20species%20and%20PCTID.%20Any%20help%20would%20be%20greatly%20appreciated.%20Thanks%2C%20Anna.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1485307%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1485349%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20INDEX%20MATCH%20VLOOKUP%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1485349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F708626%22%20target%3D%22_blank%22%3E%40anna_carolyn%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20about%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20B1%20on%20Sheet1%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DIF(COUNTIFS(Sheet2!%24B%242%3A%24B%2445%2C%24A2%2CSheet2!%24A%242%3A%24A%2445%2CB%241)%2C%22X%22%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Eand%20then%20copy%20it%20across%20and%20down.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20made%20a%20conditional%20formatting%20rule%20to%20highlight%20the%20cells%20with%20%22X%22%20in%20them.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20refer%20to%20the%20attached%20for%20more%20details.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1488125%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20INDEX%20MATCH%20VLOOKUP%20functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1488125%22%20slang%3D%22en-US%22%3E%3CP%3EYou're%20welcome%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F708626%22%20target%3D%22_blank%22%3E%40anna_carolyn%3C%2FA%3E!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi, I'm new to Excel and looking for guidance. Sorry if I'm wasting your time. I need to match the data in Sheet2 to Sheet1 so that it marks an "x" in the box for the corresponding plant species and PCTID. Any help would be greatly appreciated. Thanks, Anna.

3 Replies
Highlighted
Best Response confirmed by anna_carolyn (New Contributor)
Solution

@anna_carolyn 

 

How about this?

 

In B1 on Sheet1

=IF(COUNTIFS(Sheet2!$B$2:$B$45,$A2,Sheet2!$A$2:$A$45,B$1),"X","")

and then copy it across and down.

 

I also made a conditional formatting rule to highlight the cells with "X" in them.

 

Please refer to the attached for more details.

 

Highlighted

@Subodh_Tiwari_sktneer thank you so much, that has been a great help! 

Highlighted

You're welcome @anna_carolyn!