Forum Discussion
Solved
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...
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
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
