Home

Help! Index match?

%3CLINGO-SUB%20id%3D%22lingo-sub-797828%22%20slang%3D%22en-US%22%3EHelp!%20Index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797828%22%20slang%3D%22en-US%22%3EI%20have%20a%20file%20with%20two%20spreadsheets.%20In%20spreadsheet2%20Cell%20D5%2C%20I%20am%20trying%20to%20pull%20in%20the%20info%20from%20spreadsheet1%E2%80%99s%20column%20G%20when%20it%20matches%20with%20a%20search%20criteria%20on%20the%20same%20row%20that%20could%20be%20in%20anyone%20of%20spreadsheet%201%E2%80%99s%20column%20A%3AC.%3CBR%20%2F%3E%3CBR%20%2F%3EIn%20short%20I%E2%80%99m%20trying%20to%20match%20a%20value%20that%20could%20be%20found%20in%20multiple%20columns%20then%20pull%20in%20the%20value%20that%20is%20found%20on%20that%20same%20row%20in%20column%207.%20I%E2%80%99ve%20had%20some%20success%20but%20only%20if%20my%20match%20is%20found%20in%20the%20first%20column%20or%20I%20only%20search%20one%20column.%3CBR%20%2F%3E%3CBR%20%2F%3ESorry%20I%20am%20unable%20to%20post%20the%20spreadsheet.%201st%20time%20user.%20Any%20help%20is%20much%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-797828%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-797928%22%20slang%3D%22en-US%22%3ERe%3A%20Help!%20Index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797928%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F389997%22%20target%3D%22_blank%22%3E%40Jbones%3C%2FA%3E%26nbsp%3Bhello%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20use%20something%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Sheet1!G1%3AG10%2CMATCH(1%2CMMULT(--(D5%3DSheet1!A1%3AC10)%2CTRANSPOSE(COLUMN(Sheet1!A1%3AC10)%5E0))%2C0))%2C%22Not%20Found%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20an%20array%20formula%2C%20known%20as%20a%20%3CSTRONG%3ECSE%3C%2FSTRONG%3E%2C%20or%20a%20%3CSTRONG%3EC%3C%2FSTRONG%3EONTROL%2B%3CSTRONG%3ES%3C%2FSTRONG%3EHIFT%2B%3CSTRONG%3EE%3C%2FSTRONG%3ENTER%20formula.%20Don't%20confirm%20with%20just%20ENTER.%20When%20you%20confirm%20the%20formula%20entry%2C%20ensure%20it's%20the%20only%20cell%20selected.%20Here%20is%20a%20good%20explanation%20of%20the%20formula%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fexceljet.net%2Fformula%2Findex-and-match-on-multiple-columns%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fexceljet.net%2Fformula%2Findex-and-match-on-multiple-columns%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20don't%20want%20to%20use%20an%20array%20formula%2C%20you'll%20need%20to%20enter%20the%20ranges%20individually%2C%20something%20like%20this%3A%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CP%3E%3DIFERROR(INDEX(Sheet1!G1%3AG10%2CMAX(IFERROR(MATCH(%24D5%2CSheet1!A1%3AA10%2C0)%2C-1)%2CIFERROR(MATCH(%24D5%2CSheet1!B1%3AB10%2C0)%2C-1)%2CIFERROR(MATCH(%24D5%2CSheet1!C1%3AC10%2C0)%2C-1)))%2C%22Not%20Found%22)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20both%20of%20these%20formulas%2C%20you%20can%20see%20I%20assumed%20the%20range%20was%20in%20rows%201%20through%2010%2C%20and%20the%20sheets%20were%20default%20names.%20Adjust%20formula%20accordingly%20to%20your%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHTH%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798070%22%20slang%3D%22en-US%22%3ERe%3A%20Help!%20Index%20match%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798070%22%20slang%3D%22en-US%22%3EGot%20it!%20Thanks%20a%20lot!%3C%2FLINGO-BODY%3E
Jbones
New Contributor
I have a file with two spreadsheets. In spreadsheet2 Cell D5, I am trying to pull in the info from spreadsheet1’s column G when it matches with a search criteria on the same row that could be in anyone of spreadsheet 1’s column A:C.

In short I’m trying to match a value that could be found in multiple columns then pull in the value that is found on that same row in column 7. I’ve had some success but only if my match is found in the first column or I only search one column.

Sorry I am unable to post the spreadsheet. 1st time user. Any help is much appreciated.
2 Replies

@Jbones hello there.

 

You could use something like this:

 

=IFERROR(INDEX(Sheet1!G1:G10,MATCH(1,MMULT(--(D5=Sheet1!A1:C10),TRANSPOSE(COLUMN(Sheet1!A1:C10)^0)),0)),"Not Found")

 

This is an array formula, known as a CSE, or a CONTROL+SHIFT+ENTER formula. Don't confirm with just ENTER. When you confirm the formula entry, ensure it's the only cell selected. Here is a good explanation of the formula:

https://exceljet.net/formula/index-and-match-on-multiple-columns

 

If you don't want to use an array formula, you'll need to enter the ranges individually, something like this:

=IFERROR(INDEX(Sheet1!G1:G10,MAX(IFERROR(MATCH($D5,Sheet1!A1:A10,0),-1),IFERROR(MATCH($D5,Sheet1!B1:B10,0),-1),IFERROR(MATCH($D5,Sheet1!C1:C10,0),-1))),"Not Found")

 

In both of these formulas, you can see I assumed the range was in rows 1 through 10, and the sheets were default names. Adjust formula accordingly to your data.

 

HTH

Got it! Thanks a lot!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies