VLOOKUP where LOOKUP range is two columns

[Jon Jagd]

Hi

I need to make a VLOOKUP in a sheet which I do not own or control the layout of. Therefore I cannot add any columns in the source to assist me in my lookup.

My VLOOKUP needs to look at two values/columns since the value in one column is not a unique identifier. This is easy in the value I want to lookup but not so easy in the lookup range in the source sheet. Ideally I would like to write my formula something like this:

=VLOOKUP(A2&B2;CONCATENATE('[OtherWorkbook.xlsx]Sheet1'!$M$2:$M$9;'[OtherWorkbook.xlsx]Sheet1'!$N$2:$N$9);2;FALSE)

But that obviously does not work. Any suggetions on how to slve this?

Jon

 

[kk111]

Wow. Thank you very much for the response.

So it seems like it is almost worth it to change the format of the table instead?
I was just trying to avoid dragging and dropping the x down (because in my current table, I'd have to do that over 30 times).

Is there anything else you'd suggess other than manually dragging and dropping?

Thanks again!

If we accept the cost of having a lay-out as specified...

H2, control+shift+enter, not just enter:

=VLOOKUP(G2,OFFSET($B$2,MATCH(F2,$A$2:$A$5,0)-1,0,
   LOOKUP(9.99999999999999E+307,CHOOSE({1,2},ROWS($A$2:$A$5),
   MATCH(1,IF(1-(A2:A5=""),IF(1-(A2:A5=F2),
   IF(ROW(A2:A5)-ROW(A2)+1>MATCH(F2,$A$2:$A$5,0),1))),0)))-
   MATCH(F2,$A$2:$A$5,0)+1,2),2,0)

If we reserve a cell for the repetitive part in this formula...

I2, just enter:

=MATCH(F2,$A$2:$A$5,0)

H2, still confirmed with control+shift+enter, would become:

=VLOOKUP(G2,OFFSET($B$2,I2-1,0,
  LOOKUP(9.99999999999999E+307,CHOOSE({1,2},ROWS($A$2:$A$5),
  MATCH(1,IF(1-(A2:A5=""),IF(1-(A2:A5=F2),
  IF(ROW(A2:A5)-ROW(A2)+1>I2,1))),0)))-I2+1,2),2,0)

 

[Aladin Akyurek]

Wow. Thank you very much for the response.

You are welcome.

[uote]So it seems like it is almost worth it to change the format of the table instead?
I was just trying to avoid dragging and dropping the x down (because in my current table, I'd have to do that over 30 times). [/quote]

The 2nd set up could be admissible, that's, not too bad qua performance.

Is there anything else you'd suggess other than manually dragging and dropping?

Thanks again!

You could try recording a macro.

 

[kk111]

OK, thanks again!

You are welcome.

[uote]So it seems like it is almost worth it to change the format of the table instead?
I was just trying to avoid dragging and dropping the x down (because in my current table, I'd have to do that over 30 times).

The 2nd set up could be admissible, that's, not too bad qua performance.



You could try recording a macro.[/QUOTE]

 

[Aladin Akyurek]

OK, thanks again!
...

You are welcome.