Forum Discussion
Combining the IF and INDEX Functions
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
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
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
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
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
