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
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.

 

7 Replies

@kdwork 

Hi

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

@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

Related Conversations