+ Reply to Thread
Results 1 to 5 of 5

How can one find data in column A based on data in column B when VLookup cannot be used?

  1. #1
    Registered User
    Join Date
    10-26-2012
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    How can one find data in column A based on data in column B when VLookup cannot be used?

    How can I find the last date in Column A that has a corresponding non-zero amount in Column B? Positions of Column A and Column B cannot be swapped on the sheet, so VLookup will not work.

    I use this formula to find the last non-zero amount in Column B:
    =INDEX(B2:B10, LOOKUP(2, 1/(B2:B10<>0), ROW(B2:B10) - ROW(B2) + 1))

    Column A Column B
    Date Annual Premium
    1/26/2016 $2,813.16
    1/29/2016 $0.00
    2/23/2016 $928.08
    2/16/2016 $8,084.00
    2/25/2016 $1,276.56
    2/12/2016 $0.00
    2/29/2016 $1,077.00
    3/3/2016 $0.00
    3/6/2016 $0.00

    Thanks in advance for your advice!
    Rog
    Attached Files Attached Files

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How can one find data in column A based on data in column B when VLookup cannot be use

    Assuming the numbers in column B will always be positive...

    =LOOKUP(2,1/(B2:B96>0),A2:A96)

    Format as Date
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How can one find data in column A based on data in column B when VLookup cannot be use

    Quote Originally Posted by rkenchel View Post
    I use this formula to find the last non-zero amount in Column B:

    =INDEX(B2:B10, LOOKUP(2, 1/(B2:B10<>0), ROW(B2:B10) - ROW(B2) + 1))
    Try it like this:

    =LOOKUP(2,1/(B2:B10>0),B2:B10)

  4. #4
    Registered User
    Join Date
    10-26-2012
    Location
    California
    MS-Off Ver
    Excel 2007, 2010
    Posts
    13

    Re: How can one find data in column A based on data in column B when VLookup cannot be use

    Thanks Tony! That worked just fine!
    Rog

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How can one find data in column A based on data in column B when VLookup cannot be use

    You're welcome. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VLOOKUP - Find more data by extending row and column
    By hassanyf in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 10-28-2015, 04:25 PM
  2. [SOLVED] Based on Column Header find and copy data from one sheet to another
    By Sachy in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-31-2014, 10:00 AM
  3. Replies: 5
    Last Post: 05-14-2014, 08:35 AM
  4. [SOLVED] find data based NOT on the first column of my array
    By vizzkid in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-16-2013, 08:59 AM
  5. [SOLVED] Copy data from column to other sheets, based upon vlookup/criteria on column a
    By jedemeyer1 in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 03-27-2013, 04:01 AM
  6. Replies: 3
    Last Post: 02-08-2010, 06:18 PM
  7. Replies: 2
    Last Post: 10-26-2009, 06:43 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1