Solved

Value result from Vlookup

Posted on 2013-11-13
10
405 Views
Last Modified: 2013-11-20
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
0
Comment
Question by:Justincut
  • 5
  • 3
10 Comments
 
LVL 23

Accepted Solution

by:
NBVC earned 500 total points
ID: 39645682
It is because the contents of the cell B304 is greater than 255 characters, which VLOOKUP, MATCH, SUMIF, COUNTIF don't work with.

You can try perhaps SUMPRODUCT.. but you need to limit the range sizes to min required.

e.g

=SUMPRODUCT(--(Journals!$B$1:$B$1000=B305),Journals!$O$1:$O$1000)
0
 

Author Comment

by:Justincut
ID: 39645746
What about creating my own Vlookup via a Function inserted into myWorksheet? Would that work? What code would I need?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39645760
Not sure if a UDF would be more efficient... you can try:

=INDEX(Journals!$B$1:$B$1000,MATCH(TRUE,INDEX(Journals!$B$1:$B$1000=B304,0),0))

again limiting rows to min needed, perhaps by creating a Dynamic Named Range.
0
 

Author Comment

by:Justincut
ID: 39645861
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:B100,2)

 

 

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
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 23

Expert Comment

by:NBVC
ID: 39645885
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:B,1)

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:C,2)
0
 

Author Comment

by:Justincut
ID: 39647334
Its not working in Range "S5". Any ideas why?
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39647697
Can you clarify what you mean?  What exact formula are you using and what is expected?
0
 
LVL 81

Expert Comment

by:byundt
ID: 39647956
You might consider the following alternative to your VLOOKUP in cell T304:
=IFERROR(VLOOKUP($B304,Journals!$B:$Q,15,FALSE),IFERROR(LOOKUP(2,1/(LEFT($B304,255)=LEFT(Journals!$B:$B,255)),Journals!P:P),0))

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.
0
 
LVL 23

Expert Comment

by:NBVC
ID: 39656650
.... sorry wrong thread.....

Justincut, please close this thread as appropriate before starting new threads...
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Help with Adding text from a form to a worksheet 5 35
splitting text of cell to columns 14 22
Create Excel formula on dynamic data 5 30
Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

948 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now