Vlookup with index match but on sheet names

**Blake 7** · 02-01-2012, 04:44 AM

Hi Guys,

Hope all are well.

There is a macro within this workbook (on the summary sheet tab) which places all sheet names in col A, from a2 down.

I need to adapt the index match formula in cells B and C2 to be able to reference the values in col A, which are the sheet names, as I don’t want to have to type the sheet name each time!

=INDEX(Bob!$B:$B,MATCH(Summary!$B$1,Bob!$A:$A,0))

in the formula above I typed Bob! which is in cell a2 of the summary sheet but would like to be able to reference it using a formula.

Any ideas?

please see attached.

Thanks

**Pete_UK** · 02-01-2012, 05:30 AM

Try this:

=INDEX(INDIRECT("'"&A2&"'!$B:$B"),MATCH(Summary!$B$1,INDIRECT("'"&A2&"'!$A:$A"),0))

The apostrophes allow for your sheet names having spaces.

Hope this helps.

Pete