SOLVED
Home

Combining the IF and INDEX Functions

%3CLINGO-SUB%20id%3D%22lingo-sub-526863%22%20slang%3D%22en-US%22%3ECombining%20the%20IF%20and%20INDEX%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-526863%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20created%20a%20forecasting%20spreadsheet%20that%20provides%20me%20with%20information%20of%20different%20items%20to%20help%20determine%20production%20demand%20across%20multiple%20locations%20(see%20attached%20example).%20Our%20perpetual%20inventory%20system%20allows%20for%20the%20export%20of%20data%20to%20excel%20however%2C%20the%20list%20of%20items%20changes%20sightly%20from%20day%20to%20day%20(%2B%2C-%20one%20or%20two%20items).%20When%20the%20exported%20data%20is%20pasted%20into%20excel%2C%20those%20one%20or%20two%20items%20skew%20the%20data%2C%20making%20the%20entire%20spreadsheet%20useless.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20incorporate%20the%20use%20of%20the%20IF%20and%20INDEX%20functions%20to%20identify%20individual%20item%20numbers%20from%20the%20data%20entry%20sheet%20and%20automatically%20populate%20the%20corresponding%20%3CSTRONG%3Erow%3C%2FSTRONG%3E%20to%20another%20sheet%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20would%20eliminate%20discrepancies%20in%20the%20spreadsheet%20generated%20by%20the%20inconsistent%20item%20number%20list%20because%20excel%20would%20be%20drawing%20from%20a%20set%20table%20rather%20than%20a%20cell's%20location.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20this%20makes%20absolutely%20no%20sense%2C%20please%20let%20me%20know%20and%20thank%20you%20in%20advance%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESincerely%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-526863%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-527134%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20the%20IF%20and%20INDEX%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527134%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%2C%20that%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX('Data%20Entry'!%24D%242%3A%24H%24198%2CMATCH(%24C3%2C'Data%20Entry'!%24C%242%3A%24C%24198%2C0)%2CMATCH(D%242%2C'Data%20Entry'!%24D%241%3A%24H%241%2C0))%2C%20%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3Esee%20row%203%20in%20Site%203%20sheet%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-527498%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20the%20IF%20and%20INDEX%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-527498%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%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20sir%2C%20are%20a%20scholar%20and%20a%20gentleman.%20I%20do%20have%20one%20issue%20though%20that%20I%20still%20cannot%20figure%20out.%20The%20formula%20you%20provided%20does%20not%20distinguish%20between%20item%20numbers%20that%20have%20identical%20descriptions%20but%20different%20item%20numbers.%20See%20D9%20%26amp%3B%20D10%20on%20site%20three.%20Any%20suggestions%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EJohn%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-528789%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20the%20IF%20and%20INDEX%20Functions%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-528789%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F333039%22%20target%3D%22_blank%22%3E%40J-Griff%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20John%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20I%20missed%20that.%20Corrected%3A%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX('Data%20Entry'!%24D%242%3A%24H%24198%2CMATCH(1%2CINDEX((%24C9%3D'Data%20Entry'!%24C%242%3A%24C%24198)*(%24B9%3D'Data%20Entry'!%24B%242%3A%24B%24198)*(%24A9%3D'Data%20Entry'!%24A%242%3A%24A%24198)%2C0)%2C0)%2CMATCH(D%242%2C'Data%20Entry'!%24D%241%3A%24H%241%2C0))%2C%20%22no%20such%22)%3C%2FPRE%3E%0A%3CP%3Eit%20finds%20the%20items%20no%20if%20all%20of%20Description%2C%20Item%20%23%20and%20Location%20match%3C%2FP%3E%3C%2FLINGO-BODY%3E
J-Griff
Occasional Contributor

Hello,

 

I created a forecasting spreadsheet that provides me with information of different items to help determine production demand across multiple locations (see attached example). Our perpetual inventory system allows for the export of data to excel however, the list of items changes sightly from day to day (+,- one or two items). When the exported data is pasted into excel, those one or two items skew the data, making the entire spreadsheet useless. 

 

Is there a way to incorporate the use of the IF and INDEX functions to identify individual item numbers from the data entry sheet and automatically populate the corresponding row to another sheet? 

 

This would eliminate discrepancies in the spreadsheet generated by the inconsistent item number list because excel would be drawing from a set table rather than a cell's location.

 

If this makes absolutely no sense, please let me know and thank you in advance for your help. 

 

Sincerely,

 

John 

 

 

3 Replies

@J-Griff , that could be

=IFERROR(INDEX('Data Entry'!$D$2:$H$198,MATCH($C3,'Data Entry'!$C$2:$C$198,0),MATCH(D$2,'Data Entry'!$D$1:$H$1,0)), "no such")

see row 3 in Site 3 sheet

 

@Sergei Baklan 

 

You sir, are a scholar and a gentleman. I do have one issue though that I still cannot figure out. The formula you provided does not distinguish between item numbers that have identical descriptions but different item numbers. See D9 & D10 on site three. Any suggestions? 

 

Thank you again!

 

John 

Solution

@J-Griff 

 

Hi John,

 

Yes, I missed that. Corrected:

=IFERROR(INDEX('Data Entry'!$D$2:$H$198,MATCH(1,INDEX(($C9='Data Entry'!$C$2:$C$198)*($B9='Data Entry'!$B$2:$B$198)*($A9='Data Entry'!$A$2:$A$198),0),0),MATCH(D$2,'Data Entry'!$D$1:$H$1,0)), "no such")

it finds the items no if all of Description, Item # and Location match

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
18 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies