• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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
0
Jagwarman
Asked:
Jagwarman
1 Solution
 
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 HensonIT & Database AssistantCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
JagwarmanAuthor Commented:
That is perfect thanks for staying with it.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now