Change range on VLOOKUP

[imimin]

Hello!

How could I change the following VLOOKUP to set the range so it will stop on the last row that has data (instead of using the range $A$2:$A$10000) AND so it will auto populate that range? Is that EVEN possible? I guess that would be a macro?

=IF(COUNTIF(Sheet2!$A$2:$A$10000,A2),VLOOKUP(A2,Sheet2!$A$2:$B$10000,2,FALSE),"")

Thanks for the help!

 

[Aladin Akyurek]

Hello!

How could I change the following VLOOKUP to set the range so it will stop on the last row that has data (instead of using the range $A$2:$A$10000) AND so it will auto populate that range? Is that EVEN possible? I guess that would be a macro?

=IF(COUNTIF(Sheet2!$A$2:$A$10000,A2),VLOOKUP(A2,Sheet2!$A$2:$B$10000,2,FALSE),"")

Thanks for the help!

If the range in column A consists of text...

A1:

=MATCH(REPT("z",255),Sheet2!A:A)

If the range in column A is numeric...

A1:

=MATCH(9.99999999999999E+307,Sheet2!A:A)

Now you can invoke:

=IF(ISNUMBER(MATCH(A2,Sheet2!$A$2:INDEX(Sheet2!A:A,$A$1),0)),
      VLOOKUP(A2,Sheet2!$A$2:INDEX(Sheet2!B:B,$A$1),2,0),"")

 

[imimin]

Thank you!

 

[Aladin Akyurek]

Thank you!

You are welcome. Thanks for providing feedback.