The Reaper
New Member
- Joined
- Mar 21, 2012
- Messages
- 14
I'm having an issue concatenating 3 cells to use as the criteria for the worksheetfunction.vlookup; I'm guessing its a syntax error but I've looked high and low to figure out how to get it to work, any help is appreciated. The point of this is to concatenate 3 columns eg. "cat", "dog", "wet" as in cat.dog.wet as the criteria for the vlookup against a table where that unique concatenation returns the 9th column value to be utilized.
Example:
When I try to do a simple worksheetfunction.vlookup it works (and returns an #N/A) but I don't want to add a column that contains the concatenation of the 3 columns for the lookup criteria.
Example:
Code:
Dim wlRef as Worksheet
Dim r as range
Dim WLDperf as long, WLDfla as long
Set wlRef = ThisWorkbook.Worksheets("Reference")
WLDperf = Sheet1Cells(Sheet1.Rows.Count, "B").End(xlUp).Offset(1, 0).Row
WLDfla = Sheet1.Cells(Sheet1.Rows.Count, "B").End(xlUp).Row
Set r = Sheet1.Range("U" & WLDperf, "U" & WLDfla)
r.Value2 = Evaluate(VLookup(r.Offset(0, -17).Value & "&" & Sheet1.Range("A4").Address & "&" & r.Offset(0, -13).Value & "&" & Sheet1.Range("A4").Address & "&" & r.Offset(0, -14).Value, wlRef.Range("A:I"), 9, False))
When I try to do a simple worksheetfunction.vlookup it works (and returns an #N/A) but I don't want to add a column that contains the concatenation of the 3 columns for the lookup criteria.