Vlookup In Multiple Sheets

Hi Irem and welcome to the Forum

I suggest you don't use VLOOKUP at all for this but use the worksheet function INDIRECT instead. If you type =INDIRECT(B3) in a cell, it will show whatever is in cell B3. You can also use that across sheets and it looks to me like all sheets are standard (presumably completed by a manager then sent to you and appended to your file).

In that case a formula like

=VLOOKUP($O$1,'1304956'!$C$8:$D$15,2,FALSE)

can be replaced by a simpler formula which you just copy to other cells and fill down your whole Consolidated sheet. In that sheet of the revised file attached, I've inserted a "Helper Row" (as row 1) but that can be hidden. You'll see that yellow-filled cell O1 says D8- that's where the "Competency 01" value is sorted on the other sheets. O2 says D9 etc.

Now in the first orange-filled cell O3 (was O2-with the VLOOKUP formula above), I've made the formula this:

=INDIRECT("'"& $A3 &"'!" & O$1)

which means look as the sheet named like cell A3 (where the first bold $ above fixes the column) and show the value from the cell in O1  (the second bold $ fixing the row). You get whatever is in D8 of sheet 1304956.

I've then copied that cell and pasted the formula into the 9 rows to the right. You can then fill it down the sheet. 

I've done it as ar as row 10 only since a problem occurs however when you get to row 11 where your Vloopup (or the formula from O3) would return a "#REF!" error (since that SF ID sheet isn't yet in the file).

It's neater to avoid those problems by using the IFERROR function (wrapped around the INDIRECT formula. In the second orange-filled cell O11, the formula is (which additions in bold: 

=IFERROR(INDIRECT("'"& $A11 &"'!" & O$1),"SF ID sheet not found")

This means if the first bit (INDIRECT) gives an error, display whatever the second parameter is i.e. the more meaningful message about there being no sheet.

I've copied that right and down to row 16 only since my suggestion is that you copy that formula from O11 to O3 (clear the colour fill) then paste it right and fill down the sheet (so you always get a consistent result).

Now you have just one formula and only need to get the helper row correct to get all your results back!

Hope this makes sense and helps.