VLOOKUP - used with INDIRECT...

[Aitch]

Would be grateful for some help please!

I'm using this formula

=VLOOKUP (I2 , OLD!H$2 : OLD!M$2000 , 6 , FALSE)


I'd like to be able to input different values into the formula by using cells A1 A2 A3 A4 like this:
A1 = the I in I2
A2 = the H in OLD!H$2
A3 = the M in OLD!M$2000
A4 = the 6

How do I use INDIRECT properly to make this work, all my attempts keep showing an error!

=VLOOKUP (INDIRECT(A1)&"2 , OLD!"&(A2)&"$2 : OLD!"&(A3)&"$2000 ,"&(A4) ", FALSE")

Thank you!

 

[Fluff]

I would advise against using the indirect function in this way, especially if you are going to have a lot of cells with the Vlookup function.
Indirect is volatile & recalculates whenever any other cell changes, this in turn can potentially cause your workbook to slow down significantly.

 

[Aitch]

I would advise against using the indirect function in this way, especially if you are going to have a lot of cells with the Vlookup function.
Indirect is volatile & recalculates whenever any other cell changes, this in turn can potentially cause your workbook to slow down significantly.

Dang, so that's why lol! I use INDIRECT quite often and yes, my sheet is pretty slow...

Is there any other way to do this?

 

[Fluff]

Not that I'm aware of.

 

[Aitch]

Not that I'm aware of.

Dangit!

Can you help me with my original question then please?

 

[Fluff]

How about
=VLOOKUP(INDIRECT(A1&"2"), INDIRECT("OLD!"&A2&"$2:"&A3&"$2000"),A4, FALSE)

 

[Aitch]

How about
=VLOOKUP(INDIRECT(A1&"2"), INDIRECT("OLD!"&A2&"$2:"&A3&"$2000"),A4, FALSE)

Thank you so much bro!

I amended your formula so it uses the V column and added some $ to fix certain cells...

=IFERROR (VLOOKUP(INDIRECT(V$11& "2"), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)

It works perfectly - but when I try to copy the formula down to the rest of the column, it doesn't automatically increase the value of the Red highlighted value

I tried removing the " " but no joy... how do I make it copy down like this?

=IFERROR (VLOOKUP(INDIRECT(V$11& 2), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)
=IFERROR (VLOOKUP(INDIRECT(V$11& 3), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)
=IFERROR (VLOOKUP(INDIRECT(V$11& 4), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)

 

[Fluff]

How about
=IFERROR (VLOOKUP(INDIRECT(V$11& ROW(A2)), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)

 

[Aitch]

How about
=IFERROR (VLOOKUP(INDIRECT(V$11& ROW(A2)), INDIRECT("OLD!"&V$12&"$2:"&V$13&"$2000"),V$14, FALSE) ,0)

Perfect, I'm very grateful!


Can I quickly ask one more - I can never get the " and & right with strings... how do I make the highlighted in to a cell reference V$2?

=IFERROR(IF(VLOOKUP(NEW!X$2 &" "& V3 , NEW!A$2:A ,1,FALSE)=NEW!X$2 &" "& V3,NEW!X$2,""),"")


I tried these but with no luck...

=IFERROR(IF(VLOOKUP(NEW!X$ & V$2 &" "& V3 , NEW!A$2:A ,1,FALSE)=NEW!X$2 &" "& V3,NEW!X$2,""),"")

=IFERROR(IF(VLOOKUP(INDIRECT("NEW!X$" & V$2 &" "& V3) , NEW!A$2:A ,1,FALSE)=NEW!X$2 &" "& V3,NEW!X$2,""),"")

 

[Fluff]

Considering those formulae make absolutely no-sense, I have no idea.