Home

Excel Multistep lookup function INDEX MATCH?

%3CLINGO-SUB%20id%3D%22lingo-sub-837494%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837494%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403582%22%20target%3D%22_blank%22%3E%40CodeRed2004%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Ecan%20you%20explain%20what%20the%20expected%20result%20would%20be%20in%20the%20lower%20table%3F%20The%20top%20table%20has%20three%20rows%20with%20the%20same%20PO%20and%20UPC.%26nbsp%3B%20What%20do%20you%20want%20to%20see%20in%20the%20lower%20table%3F%20And%20how%20does%20the%20number%20574%20get%20into%20the%20Qty%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-837465%22%20slang%3D%22en-US%22%3EExcel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-837465%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20formula%20that%20looks%20up%20a%20value%20based%20upon%20several%20other%20values.%20I'm%20pretty%20sure%20that%20it%20needs%20to%20be%20a%20custom%20macro%20formula%20or%20a%20index%2Fmatch%20formula%2C%20but%20I'm%20not%20certain%20how%20to%20set%20it%20up.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELookup%20value%20in%20a%20separate%20sheet%20by%20column%20%23%20or%20header%20by%20PO%26gt%3BUPC%26gt%3BAppx%20referenced%20QTY%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20top%20table%20(book%201)%20is%20where%20I'm%20pulling%20the%20data%20from.%20The%20bottom%20(book%202)%20is%20the%20table%20where%20I%20need%20to%20make%20the%20formulas%20to%20fill%20in%20the%20ID%2FDate%20information.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3EPO%3C%2FTD%3E%3CTD%3EUPC%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID2%3C%2FTD%3E%3CTD%3EID3%3C%2FTD%3E%3CTD%3EID4%3C%2FTD%3E%3CTD%3EID5%3C%2FTD%3E%3CTD%3EID6%3C%2FTD%3E%3CTD%3EID7%3C%2FTD%3E%3CTD%3EQTY%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABX333Q%3C%2FTD%3E%3CTD%3E49494030232%3C%2FTD%3E%3CTD%3E446456433%3C%2FTD%3E%3CTD%3E464564537%3C%2FTD%3E%3CTD%3E854031034%3C%2FTD%3E%3CTD%3E786745211%3C%2FTD%3E%3CTD%3E787641335%3C%2FTD%3E%3CTD%3E547858748%3C%2FTD%3E%3CTD%3E787542122%3C%2FTD%3E%3CTD%3E1428%3C%2FTD%3E%3CTD%3E9%2F4%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABX333Q%3C%2FTD%3E%3CTD%3E49494030232%3C%2FTD%3E%3CTD%3E727872452%3C%2FTD%3E%3CTD%3E878754534%3C%2FTD%3E%3CTD%3E787864135%3C%2FTD%3E%3CTD%3E245242480%3C%2FTD%3E%3CTD%3E778524524%3C%2FTD%3E%3CTD%3E785783414%3C%2FTD%3E%3CTD%3E888745222%3C%2FTD%3E%3CTD%3E36%3C%2FTD%3E%3CTD%3E7%2F14%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABX333Q%3C%2FTD%3E%3CTD%3E49494030232%3C%2FTD%3E%3CTD%3E277878727%3C%2FTD%3E%3CTD%3E452048712%3C%2FTD%3E%3CTD%3E437837312%3C%2FTD%3E%3CTD%3E773754120%3C%2FTD%3E%3CTD%3E737387341%3C%2FTD%3E%3CTD%3E737853211%3C%2FTD%3E%3CTD%3E353530041%3C%2FTD%3E%3CTD%3E498%3C%2FTD%3E%3CTD%3E8%2F25%2F2019%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%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%3E%26nbsp%3B%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%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%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%3E%26nbsp%3B%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%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EPO%3C%2FTD%3E%3CTD%3EUPC%3C%2FTD%3E%3CTD%3EID%3C%2FTD%3E%3CTD%3EID2%3C%2FTD%3E%3CTD%3EID3%3C%2FTD%3E%3CTD%3EID4%3C%2FTD%3E%3CTD%3EID5%3C%2FTD%3E%3CTD%3EID6%3C%2FTD%3E%3CTD%3EID7%3C%2FTD%3E%3CTD%3EQTY%3C%2FTD%3E%3CTD%3EDate%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EABX333Q%3C%2FTD%3E%3CTD%3E49494030232%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%3E%26nbsp%3B%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%3E574%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-837465%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841125%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841125%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20table%20comes%20from%20two%20different%20systems.%20The%20goal%20is%20to%20match%20them%20up.%20All%20the%20data%20in%20the%20bottom%20table%20is%20already%20provided.%20I'm%20trying%20to%20fill%20in%20the%20blanks%20with%20data%20from%20the%20top%20table.%20VLOOKUP%20does%20not%20work%20because%20it%20returns%20the%20first%20result.%20I%20need%20to%20the%20result%20that%20most%20closely%20matches%20the%20QTY%20in%20the%20bottom%20table.%20In%20this%20example%20the%20right%20answer%20would%20be%20the%20third%20row%20as%20the%20difference%20in%20QTY%20is%20the%20smallest.%20I%20would%20want%20that%20rows%20date%20and%20ID%23s.%20Thanks%20for%20your%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841726%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841726%22%20slang%3D%22en-US%22%3EI'm%20using%20the%20Office%20365%20version.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841956%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841956%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403582%22%20target%3D%22_blank%22%3E%40CodeRed2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESince%20you%20are%20using%20Office%20365%2C%20you%20will%20sooner%20or%20later%20get%20the%20FILTER%20and%20SORT%20functions%20I%20have%20used%20so%20I%20have%20attached%20a%20workbook.%26nbsp%3B%20At%20the%20moment%2C%20I%20believe%20they%20are%20being%20rolled%20out%20to%20'monthly'%20update%20users.%3C%2FP%3E%3CP%3EThe%20solution%20is%20highly%20non-standard%20so%20you%20might%20well%20be%20advised%20to%20see%20what%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F7724%22%20target%3D%22_blank%22%3E%40Ingeborg%20Hawighorst%3C%2FA%3Ecomes%20up%20with.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-841724%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-841724%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403582%22%20target%3D%22_blank%22%3E%40CodeRed2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20version%20of%20Excel%20are%20you%20using%3F%3C%2FP%3E%3CP%3E%26nbsp%3BI%20filtered%20the%20Quantity%20field%20by%20PQ%20and%20UPC%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20ABS(%20FILTER(Table1%5BQTY%5D%2C%26nbsp%3B%20(Table1%5BPO%5D%3DPO)*(Table1%5BUPC%5D%3DUPC%20)%20)%20-%20QTY%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3Eand%20calculated%20the%20absolute%20difference%20between%20it%20and%20the%20target%20value%20QTY.%3C%2FP%3E%3CP%3EThen%20I%20sorted%20the%20ID%20and%20Date%20by%20these%20deviations%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3CFONT%3E%3D%20%40SORTBY(%20ID.filtered%2C%20QTY.filtered%20)%3C%2FFONT%3E%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CFONT%3Eand%20returned%20the%20value%20corresponding%20to%20the%20lowest%20deviation.%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%3EI%20haven't%20posted%20to%20workbook%20because%20the%20chance%20that%20you%20will%20be%20able%20to%20use%20it%20is%20remote.%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-842383%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-842383%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403582%22%20target%3D%22_blank%22%3E%40CodeRed2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIn%20more%20traditional%20way%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20723px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F130588i4DC814C9D11FE2ED%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformulas%20could%20be%20like%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DIFERROR(INDEX(%24D%243%3A%24L%245%2CMATCH(1%2C%20INDEX(%20(%24B8%3D%24B%243%3A%24B%245)*(%24C8%3D%24C%243%3A%24C%245)*(MIN(ABS(%24K%243%3A%24K%245-%24K8))%3DABS(%24K%243%3A%24K%245-%24K8))%2C0)%2C0)%2CMATCH(D%247%2C%24D%242%3A%24L%242%2C0))%2C%22no%20such%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3Eor%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-markup%22%3E%3CCODE%3E%3DINDEX(%24D%243%3A%24L%245%2CAGGREGATE(15%2C6%2C1%2F(%24B12%3D%24B%243%3A%24B%245)%2F(%24C12%3D%24C%243%3A%24C%245)%2F(MIN(ABS(%24K%243%3A%24K%245-%24K12))%3DABS(%24K%243%3A%24K%245-%24K12))*(ROW(%24B%243%3A%24B%245)-ROW(%24B%242))%2C1)%2CMATCH(D%2411%2C%24D%242%3A%24L%242%2C0))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-844612%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-844612%22%20slang%3D%22en-US%22%3EIn%20the%20first%20formula%20I%20changed%20the%20targets%20to%20my%20actual%20targets%20which%20are%20in%20the%20main%20table%20and%20within%20another%20sheet.%20I%20checked%20and%20made%20sure%20they%20were%20right%20several%20times%2C%20but%20not%20avail.%20It%20just%20errored.%20Any%20ideas%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-847461%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Multistep%20lookup%20function%20INDEX%20MATCH%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-847461%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403582%22%20target%3D%22_blank%22%3E%40CodeRed2004%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt's%20hard%20to%20say%20without%20seeing%20the%20formula.%20Perhaps%20you%20may%20attache%20sample%20file%20which%20is%20structured%20as%20your%20actual%20one%2C%20but%20without%20sensitive%20data.%3C%2FP%3E%3C%2FLINGO-BODY%3E
CodeRed2004
New Contributor

I need a formula that looks up a value based upon several other values. I'm pretty sure that it needs to be a custom macro formula or a index/match formula, but I'm not certain how to set it up.

 

Lookup value in a separate sheet by column # or header by PO>UPC>Appx referenced QTY

 

The top table (book 1) is where I'm pulling the data from. The bottom (book 2) is the table where I need to make the formulas to fill in the ID/Date information.

 

POUPCIDID2ID3ID4ID5ID6ID7QTYDate
ABX333Q4949403023244645643346456453785403103478674521178764133554785874878754212214289/4/2019
ABX333Q49494030232727872452878754534787864135245242480778524524785783414888745222367/14/2019
ABX333Q494940302322778787274520487124378373127737541207373873417378532113535300414988/25/2019
           
           
POUPCIDID2ID3ID4ID5ID6ID7QTYDate
ABX333Q49494030232       574 
8 Replies

Hello @CodeRed2004 ,

 

can you explain what the expected result would be in the lower table? The top table has three rows with the same PO and UPC.  What do you want to see in the lower table? And how does the number 574 get into the Qty column?

@Ingeborg Hawighorst 

 

Each table comes from two different systems. The goal is to match them up. All the data in the bottom table is already provided. I'm trying to fill in the blanks with data from the top table. VLOOKUP does not work because it returns the first result. I need to the result that most closely matches the QTY in the bottom table. In this example the right answer would be the third row as the difference in QTY is the smallest. I would want that rows date and ID#s. Thanks for your help!

@CodeRed2004 

What version of Excel are you using?

 I filtered the Quantity field by PQ and UPC

= ABS( FILTER(Table1[QTY],  (Table1[PO]=PO)*(Table1[UPC]=UPC ) ) - QTY )

and calculated the absolute difference between it and the target value QTY.

Then I sorted the ID and Date by these deviations 

= @SORTBY( ID.filtered, QTY.filtered )

and returned the value corresponding to the lowest deviation.

I haven't posted to workbook because the chance that you will be able to use it is remote.

I'm using the Office 365 version.

@CodeRed2004 

Since you are using Office 365, you will sooner or later get the FILTER and SORT functions I have used so I have attached a workbook.  At the moment, I believe they are being rolled out to 'monthly' update users.

The solution is highly non-standard so you might well be advised to see what @Ingeborg Hawighorst comes up with.

@CodeRed2004 

In more traditional way for such sample

image.png

formulas could be like

=IFERROR(INDEX($D$3:$L$5,MATCH(1, INDEX( ($B8=$B$3:$B$5)*($C8=$C$3:$C$5)*(MIN(ABS($K$3:$K$5-$K8))=ABS($K$3:$K$5-$K8)),0),0),MATCH(D$7,$D$2:$L$2,0)),"no such")

or

=INDEX($D$3:$L$5,AGGREGATE(15,6,1/($B12=$B$3:$B$5)/($C12=$C$3:$C$5)/(MIN(ABS($K$3:$K$5-$K12))=ABS($K$3:$K$5-$K12))*(ROW($B$3:$B$5)-ROW($B$2)),1),MATCH(D$11,$D$2:$L$2,0))
In the first formula I changed the targets to my actual targets which are in the main table and within another sheet. I checked and made sure they were right several times, but not avail. It just errored. Any ideas?

@CodeRed2004 

It's hard to say without seeing the formula. Perhaps you may attache sample file which is structured as your actual one, but without sensitive data.

Related Conversations
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies