vlookup
 

Hello, can someone help me with Vlookup please ?

I have a large table with dates (decreasing monthly) in column A, and debt
(also decreasing) in column F.

I want to lookup the date at which the debt falls to zero (or goes
negative), but Vlookup uses the first column as its 'search value' while I
need it to search in column F.

I could copy the dates to a new column G and search the array F1:G500, but
is there a more elegant way of doing this ?

Thanks

K

Answer: vlookup
 

Hi K,

Yes, you can use the VLOOKUP function to search for the date at which the debt falls to zero or goes negative. Here's how you can do it:

1. Insert a new column next to column F (let's say it's column G).
2. In cell G1, enter the formula "
   Code:

   ---

   =IF(F1<=0,A1,"")

   ---

   ". This formula will check if the debt in column F is less than or equal to zero. If it is, it will return the date in column A. If it's not, it will return an empty string.
3. Copy the formula down to the rest of the cells in column G.
4. Now you can use the VLOOKUP function to search for the date in column G instead of column A. The formula would be something like "
   Code:

   ---

   =VLOOKUP(0,F:G,2,FALSE)

   ---

   ". This formula will search for the value 0 in column F, and return the corresponding value in column G (which is the date at which the debt falls to zero or goes negative).

I hope this helps! Let me know if you have any questions.

Without an example workbook it's hard to provide you with the actual formula.
But if you Google "LEFT LOOKUP CHOOSE" you will find details on how to use the CHOOSE function with a VLOOKUP to do what you need.

vlookup ---- Thanks
 

Thanks, that is what I needed

K


"Spencer101" wrote in message
...

dido22;1603621 Wrote:
Hello, can someone help me with Vlookup please ?

I have a large table with dates (decreasing monthly) in column A, and
debt
(also decreasing) in column F.

I want to lookup the date at which the debt falls to zero (or goes
negative), but Vlookup uses the first column as its 'search value' while
I
need it to search in column F.

I could copy the dates to a new column G and search the array F1:G500,
but
is there a more elegant way of doing this ?

Thanks

K


Without an example workbook it's hard to provide you with the actual
formula.
But if you Google "LEFT LOOKUP CHOOSE" you will find details on how to
use the CHOOSE function with a VLOOKUP to do what you need.




--
Spencer101

Index Match
 

Hello!

VLOOKUP while popular, is not the best way to look up values anymore. Instead, you can use the INDEX and MATCHFunctions in Excel.


=INDEX( I array,MATCH(lookup value, M array, exact match))

I array - the Array, or column where you want to find the value.
look up value - the value you are looking up on
M array - the array, or column, where you are matching the value with
Exact match - 0 for exact match.

This is much better to use and would yield more accurate results. Hope this helps!