Justincut
asked on
Value result from Vlookup
Hi Guys, can anyone help me resolve why the Vlookup is failing in the T column in my spreadsheet and I get a #VALUE# result? It uses a lookup on Column B from a concatenation of cells.
DummyRec4.xlsx
DummyRec4.xlsx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Not sure if a UDF would be more efficient... you can try:
=INDEX(Journals!$B$1:$B$10
again limiting rows to min needed, perhaps by creating a Dynamic Named Range.
=INDEX(Journals!$B$1:$B$10
again limiting rows to min needed, perhaps by creating a Dynamic Named Range.
ASKER
I saw this post on google: can you adapt the Function to my spreadsheet?
Hi,
You need to write your own VLOOKUP that doesn't have this limitation.
ALT+F11 to open VB editor, right click 'ThisWorkbook and insert module and paste the code below in. Close VB editor and back on the worksheet call with the formula
=MyVlookup(A1,Sheet5!A1:B1
Where A1 is the lookup value, Sheet5A1:B100 is the lookup range and 2 is the column you want to return.
Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
Dim cl As Range
For Each cl In c.Columns(1).Cells
If UCase(Lval) = UCase(cl) Then
MyVlookup = cl.Offset(, oset - 1)
Exit Function
End If
Next
End Function
Hi,
You need to write your own VLOOKUP that doesn't have this limitation.
ALT+F11 to open VB editor, right click 'ThisWorkbook and insert module and paste the code below in. Close VB editor and back on the worksheet call with the formula
=MyVlookup(A1,Sheet5!A1:B1
Where A1 is the lookup value, Sheet5A1:B100 is the lookup range and 2 is the column you want to return.
Function MyVlookup(Lval As Range, c As Range, oset As Long) As Variant
Dim cl As Range
For Each cl In c.Columns(1).Cells
If UCase(Lval) = UCase(cl) Then
MyVlookup = cl.Offset(, oset - 1)
Exit Function
End If
Next
End Function
You would paste the Function macro into your VB editor, by holding ALT and hitting F11, then go to Insert|Module, then paste the function in the window.
Close the editor and in the sheet enter formula like:
=MyVlookup(B304,Journals!B
again, the range Journals!B:B is a whole column. You would need to reduce that for efficiency's sake.
if you want to get column C data, then change to: =MyVlookup(B304,Journals!B
Close the editor and in the sheet enter formula like:
=MyVlookup(B304,Journals!B
again, the range Journals!B:B is a whole column. You would need to reduce that for efficiency's sake.
if you want to get column C data, then change to: =MyVlookup(B304,Journals!B
ASKER
Its not working in Range "S5". Any ideas why?
Can you clarify what you mean? What exact formula are you using and what is expected?
You might consider the following alternative to your VLOOKUP in cell T304:
=IFERROR(VLOOKUP($B304,Jou
Note that IFERROR requires Excel 2007 or later.
The first IFERROR uses the VLOOKUP formula that was initially in the cell. It will be faster than an array formula if the concatenation is less than 255 characters.
If the VLOOKUP fails, the next IFERROR uses LOOKUP to return the last match for the first 255 characters in B304 matching a value in worksheet Journals column B. If a match is found, then return a value from Journals column P on that same row.
If the LOOKUP also fails, then return 0.
=IFERROR(VLOOKUP($B304,Jou
Note that IFERROR requires Excel 2007 or later.
The first IFERROR uses the VLOOKUP formula that was initially in the cell. It will be faster than an array formula if the concatenation is less than 255 characters.
If the VLOOKUP fails, the next IFERROR uses LOOKUP to return the last match for the first 255 characters in B304 matching a value in worksheet Journals column B. If a match is found, then return a value from Journals column P on that same row.
If the LOOKUP also fails, then return 0.
.... sorry wrong thread.....
Justincut, please close this thread as appropriate before starting new threads...
Justincut, please close this thread as appropriate before starting new threads...
ASKER