#1   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 14
Default 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

  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up 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.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by dido22 View Post
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.
  #4   Report Post  
Posted to microsoft.public.excel.newusers
external usenet poster
 
Posts: 14
Default 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


  #5   Report Post  
Member
 
Posts: 47
Default 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!
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup problem - unable to get the vlookup property Fred Excel Programming 2 August 22nd 08 05:23 PM
using a vlookup to enter text into rows beneath the vlookup cell Roger on Excel Excel Programming 1 November 29th 07 01:09 PM
Vlookup in vlookup - taking the result as array name SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM
Which is faster: VLOOKUP-worksheet or VB-array VLOOKUP? erikhs[_20_] Excel Programming 1 August 6th 06 06:18 PM
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) MikeR-Oz New Users to Excel 1 March 22nd 06 10:01 AM


All times are GMT +1. The time now is 03:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"