SOLVED
Home

Index Match problem

%3CLINGO-SUB%20id%3D%22lingo-sub-386812%22%20slang%3D%22en-US%22%3EIndex%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386812%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20having%20a%20problem%20with%20what%20should%20be%20a%20simple%20solution.%26nbsp%3B%20In%20the%20example%20below%2C%20I%20am%20trying%20to%20create%20an%20Index%20Match%20formula%20to%20look%20for%20one%20value%20in%20a%20table%20and%20pull%20in%20the%20Catalog%20ID.%26nbsp%3B%20For%20example%2C%20if%20I%20look%20for%20PBC-1430%2C%20I%20need%26nbsp%3B2D4%20to%20populate%20my%20cell%3A%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ECatalog%20ID%3C%2FTD%3E%3CTD%3ECust%20A%20Item%20%23s%3C%2FTD%3E%3CTD%3ECust%20B%20Item%20%23s%3C%2FTD%3E%3CTD%3ECust%20C%20Item%20%23s%3C%2FTD%3E%3CTD%3ECust%20D%20Item%20%23s%3C%2FTD%3E%3CTD%3ECust%20E%20Item%20%23s%3C%2FTD%3E%3CTD%3ECust%20F%20Item%20%23s%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EA1%3C%2FTD%3E%3CTD%3E05BLL%3C%2FTD%3E%3CTD%3E05BAA%3C%2FTD%3E%3CTD%3E10000A%3C%2FTD%3E%3CTD%3E1000AFA%3C%2FTD%3E%3CTD%3EBAT2000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E0B1%3C%2FTD%3E%3CTD%3E04BLL%3C%2FTD%3E%3CTD%3E04BAA%3C%2FTD%3E%3CTD%3E2000B%3C%2FTD%3E%3CTD%3E2000AFA%3C%2FTD%3E%3CTD%3EBATC2000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EC5%3C%2FTD%3E%3CTD%3E06B1L%3C%2FTD%3E%3CTD%3E06B1A%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPBC-1430%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E2D4%3C%2FTD%3E%3CTD%3E08D1LL%3C%2FTD%3E%3CTD%3E08D1AA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EPBC-1460%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20originally%20tried%20using%20a%20vlookup%2C%20but%20that%20didn't%20work.%26nbsp%3B%20Any%20assistant%20would%20be%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-386812%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388729%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388729%22%20slang%3D%22en-US%22%3EWelcome!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388728%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388728%22%20slang%3D%22en-US%22%3EAll%20formulas%20worked%20and%20provided%20the%20same%20results%20-%20thank%20you%20so%20much!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388727%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388727%22%20slang%3D%22en-US%22%3EThis%20formula%20worked!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388461%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388461%22%20slang%3D%22en-US%22%3ETo%20remedy%20the%20possible%20occurrence%20of%20duplicates%2C%20I%20suggest%20this%20formula%20in%20Inventory!A16%3A%3CBR%20%2F%3E%3DIFERROR(INDEX('Original%20Catalog%20%26amp%3B%20Customers'!B%246%3AB%2416%2C%3CBR%20%2F%3ESUMPRODUCT(MAX(ROW('Original%20Catalog%20%26amp%3B%20Customers'!C%246%3AI%2416)*%3CBR%20%2F%3E('Original%20Catalog%20%26amp%3B%20Customers'!C%246%3AI%2416%3DB16)))-5)%2C%3CBR%20%2F%3E%22No%20such%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-388458%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-388458%22%20slang%3D%22en-US%22%3EA%20shorter%20variant%20of%20the%20formula%20in%20Inventory!A16%20is%20this%3A%3CBR%20%2F%3E%3DIFERROR(INDEX('Original%20Catalog%20%26amp%3B%20Customers'!B%246%3AB%2416%2C%3CBR%20%2F%3ESUMPRODUCT(ROW('Original%20Catalog%20%26amp%3B%20Customers'!C%246%3AI%2416)*%3CBR%20%2F%3E('Original%20Catalog%20%26amp%3B%20Customers'!C%246%3AI%2416%3DB16))-5)%2C%3CBR%20%2F%3E%22No%20such%22)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386909%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386909%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F307417%22%20target%3D%22_blank%22%3E%40MeMacz1138%3C%2FA%3E%26nbsp%3B%2C%20the%20formula%20could%20be%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(%0A%20%20%20INDEX('Original%20Catalog%20%26amp%3B%20Customers'!%24B%246%3A%24B%2416%2CMATCH(1%2CINDEX(%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24C%246%3A%24C%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24D%246%3A%24D%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24E%246%3A%24E%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24F%246%3A%24F%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24G%246%3A%24G%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24H%246%3A%24H%2416)%2B%0A%20%20%20(%24B16%3D'Original%20Catalog%20%26amp%3B%20Customers'!%24I%246%3A%24I%2416)%2C%0A0)%2C0))%2C%20%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3EIt%20is%20assumed%20the%20ID%20is%20not%20repeated%20in%20Custom%20Item%20columns%2C%20each%20ID%20could%20be%20only%20in%20one%20of%20columns.%20Formula%20is%20in%20the%20left%20column%20of%20the%20Inventory.%20You%20need%20adjust%20the%20range%20to%20real%20ones%2C%20but%20it'll%20be%20better%20to%20transform%20Customer%20Items%20into%20the%20Excel%20Table%20or%20use%20dynamic%20named%20ranges%2C%20but%20that's%20cosmetic.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386893%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386893%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI%20figured%20an%20abbreviated%20spreadsheet%20would%20be%20easiest%2C%20which%20I%20have%20attached.%26nbsp%3B%20I%20have%20an%20inventory%20workbook%20that%20lists%20out%20all%20of%20the%20inventory%20we%20have%20on%20hand%20for%20finished%20products.%26nbsp%3B%20The%20inventory%20includes%20our%20own%20inventory%2C%20but%20also%20products%20that%20have%20been%20re-labeled%20for%20our%20customers%2C%20but%20in%20reality%20are%20one%20of%20our%20original%20catalog%20id%20numbers.%26nbsp%3B%20I%20basically%20want%20to%20be%20able%20to%20look%20up%20the%20customers%20item%20number%20in%20an%20array%20and%20then%20bring%20back%20the%20original%20catalog%20id%20that%20is%20associated%20with%20that%20customer's%20item%20number.%26nbsp%3B%20I've%20attached%20a%20shortened%20version.%26nbsp%3B%20I%20have%20been%20trying%20to%20put%20in%20a%20formula%20either%20to%20the%20right%20or%20left%20(highlighted%20in%20yellow).%26nbsp%3B%20It%20seems%20like%20it%20shouldn't%20be%20that%20complicated%2C%20but%20I%20have%20yet%20to%20figure%20out%20what%20I%20am%20doing%20wrong.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386878%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386878%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F307417%22%20target%3D%22_blank%22%3E%40MeMacz1138%3C%2FA%3E%26nbsp%3B%2C%20it%20depends%20on%20what%20you'd%20like%20to%20do.%20If%20multiple%20criteria%2C%20like%20return%20value%20from%20A%20if%20in%20G%20we%20have%20some1%20AND%20in%20C%20some2%2C%20etc.%20-%20that's%20one%20situation.%20If%20pull%20value%20not%20from%20column%20A%20but%20another%20one%20that%20will%20be%20third%20parameter%20INDEX.%20Similar%20to%20VLOOKUP%20when%20you%20have%20columns%20number%20as%20parameter.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWhat%20is%20your%20case%20exactly%3F%20Perhaps%20just%20small%20sample%20to%20explain.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386867%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386867%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20for%20responding%20so%20quickly!%26nbsp%3B%20Is%20there%20a%20way%20to%20do%20the%20lookup%20in%20all%20cells%20from%20B1%20through%20G5%3F%26nbsp%3B%20Rather%20than%20only%20looking%20in%20Column%20G%3F%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-386815%22%20slang%3D%22en-US%22%3ERe%3A%20Index%20Match%20problem%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-386815%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F307417%22%20target%3D%22_blank%22%3E%40MeMacz1138%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20is%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(%24A%242%3A%24A%241000%2CMATCH(J4%2C%24G%242%3A%24G%241000%2C0))%2C%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3Eif%20the%20value%20you%20are%20looking%20for%20is%20in%20J4%3C%2FP%3E%3C%2FLINGO-BODY%3E
MeMacz1138
Occasional Contributor

I am having a problem with what should be a simple solution.  In the example below, I am trying to create an Index Match formula to look for one value in a table and pull in the Catalog ID.  For example, if I look for PBC-1430, I need 2D4 to populate my cell:

Catalog IDCust A Item #sCust B Item #sCust C Item #sCust D Item #sCust E Item #sCust F Item #s
A105BLL05BAA10000A1000AFABAT2000 
0B104BLL04BAA2000B2000AFABATC2000 
C506B1L06B1A   PBC-1430
2D408D1LL08D1AA   PBC-1460

 

I originally tried using a vlookup, but that didn't work.  Any assistant would be much appreciated.

10 Replies

Hi @MeMacz1138 ,

 

That is like

=IFERROR(INDEX($A$2:$A$1000,MATCH(J4,$G$2:$G$1000,0)),"no such")

if the value you are looking for is in J4

@Sergei Baklan Thank you for responding so quickly!  Is there a way to do the lookup in all cells from B1 through G5?  Rather than only looking in Column G?  

@MeMacz1138 , it depends on what you'd like to do. If multiple criteria, like return value from A if in G we have some1 AND in C some2, etc. - that's one situation. If pull value not from column A but another one that will be third parameter INDEX. Similar to VLOOKUP when you have columns number as parameter.

 

What is your case exactly? Perhaps just small sample to explain.

@Sergei Baklan I figured an abbreviated spreadsheet would be easiest, which I have attached.  I have an inventory workbook that lists out all of the inventory we have on hand for finished products.  The inventory includes our own inventory, but also products that have been re-labeled for our customers, but in reality are one of our original catalog id numbers.  I basically want to be able to look up the customers item number in an array and then bring back the original catalog id that is associated with that customer's item number.  I've attached a shortened version.  I have been trying to put in a formula either to the right or left (highlighted in yellow).  It seems like it shouldn't be that complicated, but I have yet to figure out what I am doing wrong. 

@MeMacz1138 , the formula could be like

=IFERROR(
   INDEX('Original Catalog & Customers'!$B$6:$B$16,MATCH(1,INDEX(
   ($B16='Original Catalog & Customers'!$C$6:$C$16)+
   ($B16='Original Catalog & Customers'!$D$6:$D$16)+
   ($B16='Original Catalog & Customers'!$E$6:$E$16)+
   ($B16='Original Catalog & Customers'!$F$6:$F$16)+
   ($B16='Original Catalog & Customers'!$G$6:$G$16)+
   ($B16='Original Catalog & Customers'!$H$6:$H$16)+
   ($B16='Original Catalog & Customers'!$I$6:$I$16),
0),0)), "no such")

It is assumed the ID is not repeated in Custom Item columns, each ID could be only in one of columns. Formula is in the left column of the Inventory. You need adjust the range to real ones, but it'll be better to transform Customer Items into the Excel Table or use dynamic named ranges, but that's cosmetic.

A shorter variant of the formula in Inventory!A16 is this:
=IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
SUMPRODUCT(ROW('Original Catalog & Customers'!C$6:I$16)*
('Original Catalog & Customers'!C$6:I$16=B16))-5),
"No such")
Solution
To remedy the possible occurrence of duplicates, I suggest this formula in Inventory!A16:
=IFERROR(INDEX('Original Catalog & Customers'!B$6:B$16,
SUMPRODUCT(MAX(ROW('Original Catalog & Customers'!C$6:I$16)*
('Original Catalog & Customers'!C$6:I$16=B16)))-5),
"No such")
This formula worked!
All formulas worked and provided the same results - thank you so much!
Welcome!
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