SOLVED
Home

3 way lookup using index with match

%3CLINGO-SUB%20id%3D%22lingo-sub-831382%22%20slang%3D%22en-US%22%3E3%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-831382%22%20slang%3D%22en-US%22%3E%3CP%3EWe%20are%20working%20on%20a%20form%20that%20auto-populates%20info%20from%20a%20spreadsheet%20when%20info%20is%20entered.%26nbsp%3B%20I%20need%20cell%20H3%20to%20populate%20info%20based%20on%20info%20from%20cell%20B5%20('Product%20list'!B2%3ABZ2999%2CMATCH(BASF!B5%2C'Product%20list'!B2%3AB2999%2C0)%20then%20look%20at%20cell%20B7%26nbsp%3B('Product%20list'!B2%3ABZ2999%2CMATCH(BASF!B7%2C'Product%20list'!E2%3AE2999%2C0)%20then%20populate%20info%20that%20matches%20for%20cell%20G3%20-%20MATCH(BASF!G3%2C'Product%20list'!B1%3ABZ1%2C0)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20current%20formula%20is%26nbsp%3B%3DIFERROR(INDEX('Product%20list'!B2%3ABZ2998%2CMATCH(BASF!B7%2C'Product%20list'!E2%3AE2998%2C0)%2CMATCH(BASF!G3%2C'Product%20list'!B1%3ABZ1%2C0))%2C%22%22).%26nbsp%3B%20This%20works%20except%20where%20we%20have%20a%20Description%20(B7)%20that%20is%20the%20same%20in%20multiple%20places%20on%20the%20spreadsheet.%26nbsp%3B%20That%20is%20why%20I%20need%20it%20to%20look%20at%20cell%20B5%201st%2C%20then%20B7%2C%20then%20populate%20the%20correct%20info%20to%20match%20G3.%26nbsp%3B%20Is%20it%20possible%20to%20have%20a%203%20way%20match%20with%20index%2Fmatch%20or%20do%20I%20need%20to%20use%20something%20different%3F%26nbsp%3B%20To%20say%20I%20am%20a%20newbie%20is%20an%20understatement!%26nbsp%3B%20I%20appreciate%20any%20help%20you%20can%20give.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-831382%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-832314%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-832314%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%3C%2FP%3E%3CP%3EIt%20would%20be%20easier%20if%20you%20provide%20a%20sample%20Excel%20file%20to%20avoid%20random%20creation%20of%20the%20desired%20layout.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-834506%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-834506%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F365248%22%20target%3D%22_blank%22%3E%40nabilmourad%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20I%20attached%20the%20file.%26nbsp%3B%20I%20highlighted%20in%20red%20the%20culprits%20that%20are%20causing%20my%20problems%20on%20the%20product%20lists%20tab.%26nbsp%3B%20Currently%20it%20works...%20except%20it%20is%20pulling%20in%20the%201st%20GTIN%23%20it%20comes%20to%2C%20not%20the%20one%20that%20goes%20with%20the%20Brand%2FDescription%20in%20B5%2FB7%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-836255%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-836255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eths%20will%20do%20the%20trick%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DOFFSET('Product%20list'!%24A%241%3BMATCH(1%3BINDEX((TRIM(%24B%247)%3DINDEX(TRIM('Product%20list'!E1%3A%24E%2435)%3B%3B))*(UPPER(RIGHT(%24B%245%3BLEN(%24B%245)-3)%26amp%3B%22%20%22%26amp%3BLEFT(%24B%245%3B3))%3D'Product%20list'!B1%3A%24B%2435)%3B%3B)%3B0)-1%3BMATCH(%24G%243%3B'Product%20list'!%24A%241%3A%24AE%241%3B0)-1%3B1%3B1)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837549%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837549%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20there.%26nbsp%3B%20I%20think%20you%20are%20missing%20something.%26nbsp%3B%20I%20am%20getting%20this%20error%20when%20I%20try%20it.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20795px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130156iE2463A3EC66F18E8%2Fimage-dimensions%2F795x276%3Fv%3D1.0%22%20width%3D%22795%22%20height%3D%22276%22%20alt%3D%22clipboard_image_0.png%22%20title%3D%22clipboard_image_0.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-838024%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-838024%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857496%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20the%20info.%26nbsp%3B%20Had%20some%20problems%20getting%20it%20to%20work%20the%20other%20day.%26nbsp%3B%20Got%20busy%20and%20was%20just%20able%20to%20get%20back%20to%20this%20today.%26nbsp%3B%20Not%20sure%20what%20I%20was%20doing%20the%20other%20day%2C%20but%20it%20worked%20great%20today.%26nbsp%3B%20Thanks%20again.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-857903%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-857903%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EGuess%20I%20replied%20too%20fast.%26nbsp%3B%20The%20spreadsheet%20has%203%20areas%20for%20drop%20downs%20to%20come%20up%20with%20the%20GTIN%20and%20Item%23.%26nbsp%3B%20The%20formula%20you%20gave%20appears%20to%20work%20for%20most%20HarvXtra%20(D1%20drop%20down)%2C%20but%20it%20is%20not%20working%20when%20you%20choose%20hvxPRIVATE.LABEL%20(B5)%2C%20and%20variety%20drop%20down(B7)%2C%20or%20for%20and%20Conventional%20or%20Roundup.Ready%20(D1)%2C%20with%20any%20(B5)%20and%20(B7)%20options.%26nbsp%3B%20It%20is%20returning%20%23N%2FA.%26nbsp%3B%20The%20formula%20you%20gave%20is%20way%20over%20my%20head%20to%20be%20able%20to%20figure%20out%20where%20the%20problem%20is.%26nbsp%3B%20I%20suspect%20maybe%20it%20is%20too%20specific%20somewhere...%26nbsp%3B%20I%20attached%20the%20spreadsheet%20with%20your%20formulas%20in%20for%20GTIN%20and%20ITEM%23.%26nbsp%3B%20I%20expanded%20the%20'search'%20parameter%20from%20what%20you%20had%20given.%26nbsp%3B%20The%20product%20list%20tab%20that%20is%20attached%2C%20is%20not%20the%20full%20product%20list%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-859307%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-859307%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F399870%22%20target%3D%22_blank%22%3E%40kdwork%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20problem%20is%20that%20in%20the%20table%20you%20have%20hvxAMERICAS%20while%20on%20the%20first%20page%20you%20have%20AMERICAS%20HVX.%20What%20I%20have%20done%20her%20is%20to%20get%203%20letters%20from%20left%20of%20hvxAMERICAS%20to%20have%20%22hvx%22%20%2C%20then%20getting%20rest%20of%20the%20text%26nbsp%3B%20which%20is%20AMERICAS%20and%20merging%20them%20to%20form%20AMERICAShvx%2C%20converting%20this%20to%20all%20caps%20which%20results%20in%20AMERICASHVX.%20Than%20I%20search%20this%20text%20in%20the%20ProductList%20page%20B%20column%20with%20removing%20spaces%20of%20all%20the%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20what%20has%20become%20here%20is%20I%20get%203%20letters%20from%20the%20left%20of%20the%20text.%20with%20this%20option%20you%20will%20have%20VAMERICASCON%20and%20this%20text%20can%20not%20be%20found%20on%20B%20column%20of%20Product%20list.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860002%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860002%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eok%20-%20see%20attached.%26nbsp%3B%20I%20cleaned%20up%20the%20report.%26nbsp%3B%20C5%20doesn't%20start%20with%20the%20trait%2C%20it%20now%20starts%20with%20the%20brand%20for%20each%20of%20the%20Conventional%2C%20Roundup.Ready%2C%20and%20Harvxtra%20and%20matches%20Product%20list%20column%20B.%26nbsp%3B%20Don't%20think%20you%20need%20trim%20and%20len%20now%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20be%20able%20to%20have%20the%20GTIN%20and%20ITEM%23%20populate%20with%20all%20options%2C%20not%20just%20HarvXtra%20and%20that%20one%20variety%20that%20is%20duplicated%20for%20different%20Brand%2FCustomer%20.%26nbsp%3B%20So%2C%20cells%20D1%2C%20B5%2C%20and%20B7%20determine%20the%20GTIN%20%26amp%3B%20ITEM%23.%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20original%20formula%20%3DIFERROR(INDEX('Product%20list'!B2%3ABZ2998%2CMATCH(BASF!B7%2C'Product%20list'!E2%3AE2998%2C0)%2CMATCH(BASF!G3%2C'Product%20list'!B1%3ABZ1%2C0))%2C%22%22)%20worked%2C%20except%20for%20the%20items%20that%20had%20the%20same%20Variety%20name.%26nbsp%3B%20So%20I%20need%20the%20formula%20to%20also%20look%20at%20B5%20(Brand%2FCustomer)%20to%20come%20up%20with%20the%20correct%20GTIN%20%26amp%3B%20ITEM%23.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHopefully%20this%20makes%20it%20more%20straight%20forward%2C%20and%20less%20complicated%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860503%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860503%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3Eint%20the%20previous%20post%20I%20attached%20a%20file%20to%20solve%20the%20issue.%20However%20with%20you%20new%20setup%20you%20can%20use%20the%20attached%20file.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-861412%22%20slang%3D%22en-US%22%3ERe%3A%203%20way%20lookup%20using%20index%20with%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-861412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F232473%22%20target%3D%22_blank%22%3E%40erol%20sinan%20zorlu%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%26nbsp%3B%20In%20the%20previous%20message%20I%20didn't%20see%20any%20change%20in%20the%20formula%20and%20it%20was%20not%20working%20in%20your%20attachment.%26nbsp%3B%20This%20one%20appears%20to%20be%20working%20fine%20now.%26nbsp%3B%20Appreciate%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E
kdwork
Occasional Contributor

We are working on a form that auto-populates info from a spreadsheet when info is entered.  I need cell H3 to populate info based on info from cell B5 ('Product list'!B2:BZ2999,MATCH(BASF!B5,'Product list'!B2:B2999,0) then look at cell B7 ('Product list'!B2:BZ2999,MATCH(BASF!B7,'Product list'!E2:E2999,0) then populate info that matches for cell G3 - MATCH(BASF!G3,'Product list'!B1:BZ1,0)

 

My current formula is =IFERROR(INDEX('Product list'!B2:BZ2998,MATCH(BASF!B7,'Product list'!E2:E2998,0),MATCH(BASF!G3,'Product list'!B1:BZ1,0)),"").  This works except where we have a Description (B7) that is the same in multiple places on the spreadsheet.  That is why I need it to look at cell B5 1st, then B7, then populate the correct info to match G3.  Is it possible to have a 3 way match with index/match or do I need to use something different?  To say I am a newbie is an understatement!  I appreciate any help you can give.

 

11 Replies

@kdwork 

Hi

It would be easier if you provide a sample Excel file to avoid random creation of the desired layout.

Highlighted

@nabilmourad 

Ok, I attached the file.  I highlighted in red the culprits that are causing my problems on the product lists tab.  Currently it works... except it is pulling in the 1st GTIN# it comes to, not the one that goes with the Brand/Description in B5/B7

@kdwork 

Hello,

 

ths will do the trick

 

=OFFSET('Product list'!$A$1;MATCH(1;INDEX((TRIM($B$7)=INDEX(TRIM('Product list'!E1:$E$35);;))*(UPPER(RIGHT($B$5;LEN($B$5)-3)&" "&LEFT($B$5;3))='Product list'!B1:$B$35);;);0)-1;MATCH($G$3;'Product list'!$A$1:$AE$1;0)-1;1;1)

Hi there.  I think you are missing something.  I am getting this error when I try it.

clipboard_image_0.png

Solution

@kdwork 

Please check the attached file.

@erol sinan zorlu 

Thanks for the info.  Had some problems getting it to work the other day.  Got busy and was just able to get back to this today.  Not sure what I was doing the other day, but it worked great today.  Thanks again.

@kdwork 

Guess I replied too fast.  The spreadsheet has 3 areas for drop downs to come up with the GTIN and Item#.  The formula you gave appears to work for most HarvXtra (D1 drop down), but it is not working when you choose hvxPRIVATE.LABEL (B5), and variety drop down(B7), or for and Conventional or Roundup.Ready (D1), with any (B5) and (B7) options.  It is returning #N/A.  The formula you gave is way over my head to be able to figure out where the problem is.  I suspect maybe it is too specific somewhere...  I attached the spreadsheet with your formulas in for GTIN and ITEM#.  I expanded the 'search' parameter from what you had given.  The product list tab that is attached, is not the full product list

@kdwork 

the problem is that in the table you have hvxAMERICAS while on the first page you have AMERICAS HVX. What I have done her is to get 3 letters from left of hvxAMERICAS to have "hvx" , then getting rest of the text  which is AMERICAS and merging them to form AMERICAShvx, converting this to all caps which results in AMERICASHVX. Than I search this text in the ProductList page B column with removing spaces of all the values.

 

So what has become here is I get 3 letters from the left of the text. with this option you will have VAMERICASCON and this text can not be found on B column of Product list.

 

@erol sinan zorlu 

ok - see attached.  I cleaned up the report.  C5 doesn't start with the trait, it now starts with the brand for each of the Conventional, Roundup.Ready, and Harvxtra and matches Product list column B.  Don't think you need trim and len now?

 

I need to be able to have the GTIN and ITEM# populate with all options, not just HarvXtra and that one variety that is duplicated for different Brand/Customer .  So, cells D1, B5, and B7 determine the GTIN & ITEM#. 

My original formula =IFERROR(INDEX('Product list'!B2:BZ2998,MATCH(BASF!B7,'Product list'!E2:E2998,0),MATCH(BASF!G3,'Product list'!B1:BZ1,0)),"") worked, except for the items that had the same Variety name.  So I need the formula to also look at B5 (Brand/Customer) to come up with the correct GTIN & ITEM#.

 

Hopefully this makes it more straight forward, and less complicated?

Hello,

int the previous post I attached a file to solve the issue. However with you new setup you can use the attached file.

@erol sinan zorlu 

Thank you.  In the previous message I didn't see any change in the formula and it was not working in your attachment.  This one appears to be working fine now.  Appreciate your help!

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