Solved

Problems with Vlookup

Posted on 2013-11-16
10
1,575 Views
Last Modified: 2013-11-22
HI Guys, I have a Vlookup that does not work as there is a 256 limit in Excel.Would the formula work if I did Left(Range, 255) on the cell the vlookup is on? Some of the cells I vlookup are 400 characters and I get a "#VALUE" error message as a result,
0
Comment
Question by:Justincut
  • 6
  • 4
10 Comments
 
LVL 80

Expert Comment

by:byundt
ID: 39653969
No. Both the lookup parameter and the first column of the lookup table need to be less than 256 characters. This is a "feature" of Microsoft Excel.

If you are doing a concatenation of lookup parameters so you can use VLOOKUP on what would otherwise be a multi-criteria lookup, you can use an array formula instead.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39653975
Here is a LOOKUP formula that does the same type of thing that a concatenated lookup parameter would do with VLOOKUP. It gets around the 256 character limit because each criteria is less than 256 characters. It does not need to be array-entered (like SUMPRODUCT) although it is an array formula in essence. It returns a value from column C where column A equals "a" and column B equals 4.
=LOOKUP(2,1/((A$1:A$1000="a")*(B$1:B$1000=4)),C$1:C$1000)

Though the formula only shows two criteria, you can put as many criteria in the denominator of the second parameter as you like. Just surround each Boolean expression by parentheses, then multiply them all together.

To help understand the formula, if there is a match, the second parameter will equal 1. If there is no match, the second parameter will return an error value. Since LOOKUP is searching for a number, it will ignore text and error values. It therefore finds the row in which all criteria are satisfied, and returns a value from a different column in that row.

LOOKUP was originally intended for bracket pricing (approximate) lookups. If you read Microsoft's documentation for the function, you may draw the misleading conclusion that the second parameter has to be sorted in ascending order. For the suggested formula, however, 2 is larger than any possible value returned by the second parameter, so the sorting requirement is moot.

Note that LOOKUP will return the last match if more than one exists.
0
 

Author Comment

by:Justincut
ID: 39655805
Can you tell me how to overcome the VALUE error message in Range ("AD5") of this attachment? Its baiscally looking up the same tab but the Previous days. We roll it on a daily basis.
DummyRec6.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39658005
VLOOKUP will produce a #VALUE! error if either the lookup parameter or the first column of the lookup table has more than 255 characters. To overcome this, you must not use VLOOKUP.

Each of the VLOOKUP calls in the formula of cell AD5 can be rewritten as a LOOKUP formula like:
=LOOKUP(2,1/(('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!A$1:A$1000=A5)*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!E$1:E$1000=E5))*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!F$1:F$1000=F5))*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!G$1:G$1000=G5))*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!H$1:H$1000=H5))*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!I$1:I$1000=I5))*('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!J$1:J$1000=J5)),INDEX('[Murex BS rec breaks - 111113KMH.xlsx]Rec'!B$1:AL$1000,,AD$1))

You didn't post workbook Murex BS rec breaks - 111113 KMH.xlsx so I guessed how column B in the Rec worksheet of that workbook is determined.

Given the length of the above formula, you may want to use a UDF instead with formula like:
=LookupAlt('[Murex BS rec breaks - 111113 KMH.xlsx]Rec'!$B$1:$AL$1000,AD$1,1,A5,5,E5,6,F5,7,G5,8,H5,9,I5,10,J5)
You can use LookupAlt just like the VLOOKUP in your AD5 formula. Just correct the column assignments.

Do not use LookupAlt on entire columns! Doing so will give excruciatingly slow recalc times.

'Code goes in regular module sheet
Function LookupAlt(LookupTable As Range, ResultColumn As Long, CritCol1 As Long, Crit1, CritCol2 As Long, Crit2, _
                CritCol3 As Long, Crit3, CritCol4 As Long, Crit4, CritCol5 As Long, Crit5, CritCol6 As Long, Crit6, _
                CritCol7 As Long, Crit7)
Dim rg1 As Range, rg2 As Range, rg3 As Range, rg4 As Range, rg5 As Range, rg6 As Range, rg7 As Range, rgResults As Range
Set rg1 = LookupTable.Columns(CritCol1)
Set rg2 = LookupTable.Columns(CritCol2)
Set rg3 = LookupTable.Columns(CritCol3)
Set rg4 = LookupTable.Columns(CritCol4)
Set rg5 = LookupTable.Columns(CritCol5)
Set rg6 = LookupTable.Columns(CritCol6)
Set rg7 = LookupTable.Columns(CritCol7)
Set rgResults = LookupTable.Columns(ResultColumn)
LookupAlt = Application.Lookup(2, 1 / ((rg1 = Crit1) * (rg2 = Crit2) * (rg3 = Crit3) * (rg4 = Crit4) * (rg5 = Crit5) * (rg6 = Crit6) * (rg7 = Crit7)), rgResults)
End Function

Open in new window

0
 

Author Comment

by:Justincut
ID: 39660454
Hi Guys, can you cut and paste the Rec tab onto the same spreadsheet and then do I Vlookup. What I usually do is roll the Rec on a Daily Basis and do a Vlookup to the Previous business Day. I don't know why but when I put Left(Concatenate(A5, etc), 250) in column B, the Vlookup seems to work.Any ideas why?
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 80

Accepted Solution

by:
byundt earned 500 total points
ID: 39660648
VLOOKUP requires that the search value (first parameter) and lookup column (in lookup table) be less than 255 characters in length. By using LEFT, you truncate the strings to satisfy that constraint.

If you copy the Rec worksheet, you will need to point your VLOOKUP formulas to that worksheet. I suggest instead copying the data from the Rec worksheet and pasting it in a "Previous Day" worksheet in the VLOOKUP workbook. In doing so, you won't need to change any of your formulas. They will all be set up to point to the Previous Day worksheet, and don't need to be changed for the new data.
0
 

Author Comment

by:Justincut
ID: 39662360
If you look at my attachment I have put a LEFT formula in the B column and all the VALUEs from the AD column have disappeared. How come?
DummyRec6.xlsx
0
 
LVL 80

Expert Comment

by:byundt
ID: 39665151
Column AD appears to have values in all the cells. Which ones did you think were missing in the workbook you attached?
0
 

Author Comment

by:Justincut
ID: 39665266
I mean all the "#VALUE#" error messages have disappeared. According to you, the LEFT (Range, 250) Formula should not be working.
0
 
LVL 80

Expert Comment

by:byundt
ID: 39670599
The VLOOKUP(LEFT(Range,255),lookup table,...) formula is in fact failing in all 30 cases where column B length would otherwise exceed 255 characters without the LEFT.

Column AD formulas are working despite this failure because the maximum length in column C is 41 characters. So the alternative VLOOKUP formulas are all working.

I broke the formula in cell AD5 up into 5 lines for ease of explanation:
=IF(ISNA(VLOOKUP($B5,'Prev Day'!$B:$AL,AD$1,FALSE)),
         IF($J5="","NEW",
                     IF(ISNA(VLOOKUP($C5,'Prev Day'!$C:$AL,AD$1-1,FALSE)),"NEW",
                                       VLOOKUP($C5,'Prev Day'!$C:$AL,AD$1-1,FALSE))),
         VLOOKUP($B5,'Prev Day'!$B:$AL,AD$1,FALSE))

Line 1 VLOOKUP returns #N/A! error value because column B on worksheet Prev Day hasn't been truncated to 255 characters. Even if there were a match for the first 255 characters, the VLOOKUP on line 1 won't find it.

The line 2 test for J5 being blank fails, so control passes to the IF(ISNA(VLOOKUP... on line 3. This VLOOKUP works because C5 is both short and always found. As a result, the formula returns the value from the VLOOKUP on line 4.

Brad
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

747 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

10 Experts available now in Live!

Get 1:1 Help Now