Solved

# vlookup with a difference

Posted on 2014-09-03
99 Views
Is it posible in VBA code to Vlookup two cells that are in Sheet2 with two cells that are in sheet1

i.e instead of concatenating H2&S2 in Sheet2 and O2&AL2 in Sheet1 and then looking up the result of the concatenation from Sheet2 in Sheet1

Is it possible using VBA code to do the lookup?

0
Question by:Jagwarman

LVL 11

Expert Comment

It is probably more efficient if you use helper columns that do the concatenation and then do a simple Find using VBA. If you record a macro setting up the find parameters you should be able to pull the right pieces out of the recorded macro.
0

LVL 31

Expert Comment

You only have to do the concatenation in one sheet, the sheet in which you will be looking; you can do one of the concatenations within the vlookup:

=VLOOKUP(O2&AL2,Sheet1!A1:Z500,2,FALSE)

Where column A in Sheet1 has the concatenated values from columns H & S.

How many instances of the concatenated values will there be and in what format is the required result, number or text?

If the required result is a number and the number of instances of the concatenations is only one, then you can use the SUMIFS function without need for concatenation in either sheet. If this is an option, let us know which column the result will be coming from and we can formulate the SUMIFS function for you.

Thanks
Rob H
0

LVL 47

Expert Comment

Hi,

You could do it with an array formula (Ctrl-Shift-Enter)

=INDEX(\$H\$2:\$H\$4&\$S\$2:\$S\$4,MATCH(H2&S2,Sheet2!\$O\$2:\$O\$4&Sheet1!\$A\$L2:\$AL\$4,0))

VBA

Res = Evaluate("INDEX(\$H\$2:\$H\$4&\$S\$2:\$S\$4,MATCH(H2&S2,Sheet1!\$O\$2:\$O\$4&Sheet2!\$AL\$2:\$AL\$4,0))")

Regards
0

Author Comment

Hi Rob H

I tried your solution and I think either I am not understanding it or you have mis understood my requirement [probably because I did not make it clear enough]

the sheet I would put the VLookup on is sheet 2 and I will be looking for the data in Sheet 1

I therefore tried to change your formula but failed miserably
0

Author Comment

jkpieterse I am trying to avoid the use of helper columns
0

Author Comment

Rgonzo yours completely lost me. What does Res mean sorry for being dumb.
0

LVL 47

Expert Comment

Res is just a variable where you store the result of the Evaluate function
0

Author Comment

I am still lost Rgonzo

so in VBA would I put

Sheets("Sheet2").Select

Res = Evaluate("INDEX(\$H\$2:\$H\$4&\$S\$2:\$S\$4,MATCH(H2&S2,Sheet1!\$O\$2:\$O\$4&Sheet2!\$AL\$2:\$AL\$4,0))")

I tried that and nothing happened?
0

LVL 47

Expert Comment

Ajust the rows to match the rows of the table replace 2 with the first row of the table and 4 with the last row of the table

0

Author Comment

I made progress Rgonzo but...... I get #VALUE!
0

LVL 47

Expert Comment

pls send a dummy
0

Author Comment

I think I am the dummy :-)

ok will do tks
0

Author Comment

I just did it in a Dummy workbook and it worked. So it is me thats the dummy.
0

Author Comment

on further investigation it's not [completely] me

file attached
rgonzofile.xlsm
0

LVL 47

Accepted Solution

Hi,

pls try

Sub xxxx()
Dim lastRow

lastRow = Sheets("Sheet2").Range("H" & Rows.Count).End(xlUp).Row
Sheets("Sheet2").Range("AD2").FormulaArray = "=INDEX(\$H\$2:\$H\$" & lastRow & "&\$S\$2:\$S\$" & lastRow & ",MATCH(H2&S2,Sheet1!\$O\$2:\$O\$" & lastRow & "&Sheet1!\$AL\$2:\$AL\$" & lastRow & ",0))"

End Sub

Regards
rgonzofileV1.xlsm
0

Author Comment

That is perfect thanks for staying with it.
0

## Featured Post

Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …