I am using a shared workbook and cannot use VBA functions to use VLOOKUP for multiple sheets. I tried nesting IF Formulas but keep getting errors. Does anyone know a way to lookup information on multiple sheets in a workbook without using a VBA Function.

I think we need a little more detail as to what to you are trying to do.

There are 5 sheets in a shared workbook. Each sheet is maintained by a seperate person. I've created a VLookup that pulls all the information into a single sheet, by assigning ID numbers to each item and doing a lookup for Sheet A for IDS 1-500. Then a Vlookup for Sheet B IDs 501-1000, etc. The problem is that if someone adds item 501 to Sheet A a formula changes the starting # on Sheet B to 502 but because the VLookup is looking for ID 501 in Sheet B and results in an error. If there was a way to look for an id on any of the 4 sheets I wouldn't have to redo the lookup everytime anyone adds something. Currently I added in blank rows with ID #'s so people can add without messing up the VLOOKUP. It is a poor solution though.

I'm not going to take credit for this (other than the research part) and I'm not going to try and explain it. I'll simply say that this array formula seems to work: =VLOOKUP(C2,INDIRECT("'"&INDEX(MySheets,MATCH(1,--(COUNTIF(INDIRECT("'"&MySheets&"'!A1:A20"),C2)>0),0))&"'!A1:B20"),2,0) Use Ctrl-Shift-Enter Where: - C2 is the lookup_value - MySheets is a named range that refers to a list of sheet names, e.g. ={"Sheet1";"Sheet2";"Sheet3"} - A1:A20 is the range (common to all sheets) in which you expect to find the lookup_value - A1:B20 is the lookup_array

As long as you only have 5 sheets, you can use a Nested IF. However, once you hit the 7 Nest limit, you'll need to use the array formula above. =IF(ISNA(VLOOKUP(C2,Sheet1!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet2!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet3!A1:B2,2,0)),IF(ISNA(VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet5!A1:B2,2,0),VLOOKUP(C2,Sheet4!A1:B2,2,0)),VLOOKUP(C2,Sheet3!A1:B2,2,0)),VLOOKUP(C2,Sheet2!A1:B2,2,0)),VLOOKUP(C2,Sheet1!A1:B2,2,0)) If you keep getting errors, it's probably due to the placement of the parenthesis.

Thanks for the help. The second option worked with the nested formula not the ranges. Couldn't get ranges to give me anything except errors (I did name my ranges so not sure why the issue). But thanks all.

There was no need to name your ranges. You were supposed to create a named range that contain only the sheets names like in my example. In other words, if your sheet tabs had names like Sales, Debits, and Income, then In the Refers to box you would type: ={"Sales";"Debits";"Income"} and give it a name such as MySheets The INDIRECT function would then use this array of sheet names to reference ranges such as Sales!A1:A20, Debits!A1:A20, etc. I built a 5 sheet workbook and the formula was able to pull data from all 5 sheets as advertised.