asked on

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

Jan Karel Pieterse

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.

Rob Henson

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

Rgonzo1971

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

Jagwarman

ASKER

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

Jagwarman

ASKER

jkpieterse I am trying to avoid the use of helper columns

Jagwarman

ASKER

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

Rgonzo1971

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

Jagwarman

ASKER

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?

Rgonzo1971

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))")

Jagwarman

ASKER

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

Rgonzo1971

pls send a dummy

Jagwarman

ASKER

I think I am the dummy :-)

ok will do tks

Jagwarman

ASKER

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

Jagwarman