vlookup table array until the very last cell with value

**cgaisano** · 04-16-2013, 09:32 PM

Hi all,

I am looking into a way that the vlookup table array can be flexible to the very last cell with values.

For example:

A1:A10 - this is assuming the value is until A10 and all cells below A10 is blank.

But my spreadsheet is always changing so sometimes it is upto A20, A30 or less than A10.

I have considered using A:A which is selecting entire column but due to big file size, excel is running very slow.

Any suggestions? Thanks in advance!

**vlady** · 04-16-2013, 09:57 PM

don't know if this could help on file size and slowness of excel in your end.

=VLOOKUP(F4,INDIRECT("B1:C"&MATCH(9.99999999999999E+307,B1:B200)),2,FALSE)
note the B1-B200...this insure that the match will just go up to 200 rows down

there are other function aside from match() -lookup also could return the last non blank cell
LOOKUP(2,1/(B1:B200<>""),B1:B200)

**cgaisano** · 04-17-2013, 01:11 AM

Hi Vlady, thanks for the help I think the vlookup formula you gave me might be the key.

just to clarify what does the "B1:C" stands for in the vlookup formula?

=VLOOKUP(F4,INDIRECT("B1:C"&MATCH(9.99999999999999E+307,B1:B200)),2,FALSE)

**humdingaling** · 04-17-2013, 02:39 AM

its forms part of the table array
MATCH gives you last row
INDIRECT must be included or else the formula doesn't work

if there is 16 rows
B1:C16 is your table array