vlookup with a difference

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?

Thanks in advance
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkpieterseCommented:
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
Rob HensonFinance AnalystCommented:
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
Rgonzo1971Commented:
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

JagwarmanAuthor Commented:
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
JagwarmanAuthor Commented:
jkpieterse I am trying to avoid the use of helper columns
0
JagwarmanAuthor Commented:
Rgonzo yours completely lost me. What does Res mean sorry for being dumb.
0
Rgonzo1971Commented:
Res is just a variable where you store the result of the Evaluate function
0
JagwarmanAuthor Commented:
I am still lost Rgonzo

so in VBA would I put

Sheets("Sheet2").Select
Range("AD2").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
Rgonzo1971Commented:
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

Sheets("Sheet2").Range("AD2").Value  = Evaluate("INDEX($H$2:$H$4&$S$2:$S$4,MATCH(H2&S2,Sheet1!$O$2:$O$4&Sheet1!$AL$2:$AL$4,0))")
0
JagwarmanAuthor Commented:
I made progress Rgonzo but...... I get #VALUE!
0
Rgonzo1971Commented:
pls send a dummy
0
JagwarmanAuthor Commented:
I think I am the dummy :-)

ok will do tks
0
JagwarmanAuthor Commented:
I just did it in a Dummy workbook and it worked. So it is me thats the dummy.
0
JagwarmanAuthor Commented:
on further investigation it's not [completely] me

file attached
rgonzofile.xlsm
0
Rgonzo1971Commented:
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))"
Sheets("Sheet2").Range("AD2:AD" & lastRow).FillDown

End Sub

Regards
rgonzofileV1.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
That is perfect thanks for staying with it.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.