vlookup help

[chungtinhlak]

I have to look up for a value using vlookup in multiple sheets(all sheets) except for current sheets.

Is there a way to do that?

thanks

[mdmackillop]

Not with vlookup. Do you need a dynamic link or can you run a macro/event code to return values?

[chungtinhlak]

I found this and it works great, just really slow.

=VLOOKUP(A2,INDIRECT("'"&INDEX({"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5 ";"Sheet6";"Sheet7";"Sheet8"},MATCH(1,--(COUNTIF(INDIRECT("'"&{"Sheet1";"Sheet2";"Sheet3";"Sheet4";"Sheet5";"Sheet6 ";"Sheet7";"Sheet8"}&"'!A2:A200"),A2)>0),0))&"'!A2:C200"),2,0)

Source
http://www.eggheadcafe.com/conversat...eadid=29322760

[mdmackillop]

I'm wrong again!

Here's a UDF solution to try
[vba]
Option Explicit
Function VV(Srch, shts, cols, col)
Dim sh1 As Long, sh2 As Long, i As Long, x
sh1 = Sheets(Split(shts, ":")(0)).Index
sh2 = Sheets(Split(shts, ":")(1)).Index
For i = sh1 To sh2
x = Application.VLookup(Srch, Sheets(i).Columns(cols), col, False)
If IsNumeric(x) Then
VV = x
Exit Function
End If
Next
VV = "N/A"
End Function

[/vba]