I have done this before so know it can be done but I've been banging my head trying to remember the required syntax.
I'm working on a worksheet with a simple VLOOKUP.
=VLOOKUP(E5,$F$5:$H$90000,3,0)
My previous colleague set the last row to 90000 knowing that we would never need 90000 rows. This worksheet usually has fewer than 10000.
I want to insert a COUNTA to see how many rows in column E contain data to limit the scope of the VLOOKUP.
What I have attempted, and seem to remember doing, is something along the lines of
=VLOOKUP(E5,"F5:H"&COUNTA(E5:E90000),3,0)
Alternatively, I have been attempting to create the COUNTA in a separate cell and have the VLOOKUP pull the number of rows from here but couldn't get this to work either
Any help appreciated!
Gar
I'm working on a worksheet with a simple VLOOKUP.
=VLOOKUP(E5,$F$5:$H$90000,3,0)
My previous colleague set the last row to 90000 knowing that we would never need 90000 rows. This worksheet usually has fewer than 10000.
I want to insert a COUNTA to see how many rows in column E contain data to limit the scope of the VLOOKUP.
What I have attempted, and seem to remember doing, is something along the lines of
=VLOOKUP(E5,"F5:H"&COUNTA(E5:E90000),3,0)
Alternatively, I have been attempting to create the COUNTA in a separate cell and have the VLOOKUP pull the number of rows from here but couldn't get this to work either
Any help appreciated!
Gar