Match Index Function for Coming from Multiple Worksheets

[maeyks]

Hello to all,

I am using this formula:

=IFERROR(INDEX('[Pie Face Central ******* Food Costing File.xlsx]Recipe'!$L$2:$L$3100,MATCH(U466,'[Pie Face Central ******* Food Costing File.xlsx]Recipe'!$H$2:$H$3100,FALSE),1),"")

My question is, if I have the reference cell located on different worksheet (but has no duplicate, other than the RECIPE worksheet), what will be the formula I will use? That it will just simply go through all the worksheets locating the said reference cell?

Thank you.

 

[AFPathfinder]

You mean checking multiple sheets for a value? You'd have to have the formula explicitly check each sheet (using IF/IFERROR statements or concatenation) or use VBA.

 

[maeyks]

Pathfinder,

Yes, its grouped in different sheets, but there is not repetition. Not really a VBA expert here. Trying to figure that one out.
Thanks

 

[AFPathfinder]

If you don't have many sheets to check, you could just copy that entire formula and paste it into the error part of that IFERROR function (nesting).

[COLOR=#333333]=IFERROR(INDEX('[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]1[/B][/COLOR][COLOR=#333333]'!$L$2:$L$3100,MATCH(U466,'[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]1[/B][/COLOR][COLOR=#333333]'!$H$2:$H$3100,FALSE),1),
[/COLOR][COLOR=#333333]IFERROR(INDEX('[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]2[/B][/COLOR][COLOR=#333333]'!$L$2:$L$3100,MATCH(U466,'[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]2[/B][/COLOR][COLOR=#333333]'!$H$2:$H$3100,FALSE),1),[/COLOR][COLOR=#333333]
[/COLOR][COLOR=#333333]IFERROR(INDEX('[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]3[/B][/COLOR][COLOR=#333333]'!$L$2:$L$3100,MATCH(U466,'[/COLOR][COLOR=#333333]Recipe[/COLOR][COLOR=#0000ff][B]3[/B][/COLOR][COLOR=#333333]'!$H$2:$H$3100,FALSE),1),[/COLOR][COLOR=#333333]"")[/COLOR][COLOR=#333333])[/COLOR][COLOR=#333333])[/COLOR]

The VBA solution would loop through all sheets and use the Cells.Find function to locate the value and then take action as necessary. The next question for the VBA solution is to identify when the macro should be run and to what extent (does it run this Find action on multiple rows, active row, or does it need to identify missing values).

 

[wmunsey81]

I actually just did this on a worksheet I just did. I will give you what I have (taking out the static IP of my computer of course):

=IF(D305="L",IFERROR(INDEX('\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[Lighting Repair Log Master 7.0 working.xlsm]Filterable'!$B:$B,MATCH(G305,'\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[Lighting Repair Log Master 7.0 working.xlsm]Filterable'!$J:$J,0)),"NEEDS TICKET"),IF(D305="A",IFERROR(INDEX('\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[Audio Repair Log Master 7.0 Sterling Edition.xlsm]Filterable'!$B:$B,MATCH(G305,'\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[Audio Repair Log Master 7.0 Sterling Edition.xlsm]Filterable'!$J:$J,0)),"NEEDS TICKET"),IF(D305="G",IFERROR(INDEX('\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[General Repair Log Master 7.1.xlsm]Filterable'!$B:$B,MATCH(G305,'\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[General Repair Log Master 7.1.xlsm]Filterable'!$J:$J,0)),"NEEDS TICKET"),IF(D305="V",IFERROR(INDEX('\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[VSE Repair Log Master 7.0 Sterling Edition.xlsm]Filterable'!$B:$B,MATCH(G305,'\\IP ADDRESS HERE\1918 repair folder\Bill\Repair Log Pull File\[VSE Repair Log Master 7.0 Sterling Edition.xlsm]Filterable'!$J:$J,0)),"NEEDS TICKET"),""))))

Should probably also follow up that this formula searches for ticket numbers(column B) based of barcodes in my document (Column D) and matches them in the document searching in column J.

Hope this helps.

Actually, if anyone has a better solution to what I did, I'm all ears.