PDA

View Full Version : vlookup help



chungtinhlak
04-15-2009, 08:52 AM
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
04-15-2009, 10:09 AM
Not with vlookup. Do you need a dynamic link or can you run a macro/event code to return values?

chungtinhlak
04-15-2009, 10:14 AM
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/conversation.aspx?messageid=29322766&threadid=29322760

mdmackillop
04-15-2009, 11:01 AM
I'm wrong again!

Here's a UDF solution to try

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