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!