MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookup headache


Posted by DandBS on August 20, 2001 5:51 AM

I'm trying to use the vlookup function to summarise results from different worksheets, where:

=VLOOKUP(C3,Sheet1!$A$1:$B$8,2,FALSE)

refers to a cell as a column header with text 'Sheet1' in it.The next column header to it would have (e.g.) Sheet2. So, by dragging the cell with the formula across to the next cell, the formula would be revised to =VLOOKUP(C3,Sheet2!$A$1:$B$8,2,FALSE)

All Excel does is print the line out, rather than calculating the value, and I can't get excel to recognise this as a formula. Any ideas?


Posted by Aladin Akyurek on August 20, 2001 6:09 AM

It seems you have listed all the relevant sheet names across a row. And, every sheet has data in its $A$1:$B$8 range. C3 is the lookup value that you use for every sheet. If so, use:

=VLOOKUP($C$3,INDIRECT(D1&"!$A$1:$B$8"),2,0)

where D1 contains "Sheet1", E1 "Sheet2", etc.

If, say, D1 contains "Sheet1!$A$1:$B$8", E1 "Sheet2!$A$1:$B$8" etc., use:

=VLOOKUP($C$3,INDIRECT(D1),2,0)

0 as the 4th arg of VLOOKUP means FALSE.

Aladin

=============