+ Reply to Thread
Results 1 to 8 of 8

Search a cell upwards for a value

  1. #1
    Registered User
    Join Date
    07-15-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Search a cell upwards for a value

    Hello,

    I have a spreadsheet (screenshot included) with three columns:

    Column A:Number
    Column B:Letter
    Column C: Concatenate(Number,Letter)

    Concatenate screenshot.png

    In column C, I want to concatenate the Number and Letter columns by row. Let's use row 2 as an example. Obviously, to do a simple concatenation, you would just use the formula =concatenate(A2,B2). However, instead of using the value 'A2', I want this to be a lookup function where the lookup searches A2 for a value; if A2 has a value, then the first concatenated parameter will equal A2. If A2 has no value, then the parameter will search upwards in the A column for a value and will use that value for the concatenation.

    What function can I use to look at a cell, use the value if there is one, and if there isn't, look upwards in the column until a value is found and use that one?

  2. #2
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Search a cell upwards for a value

    Try this in C2 and copy down


    =LOOKUP(10^308,A$2:A2)&B2
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  3. #3
    Registered User
    Join Date
    07-15-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search a cell upwards for a value

    Dude! That's just what I was looking for. Thanks!

  4. #4
    Registered User
    Join Date
    07-15-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search a cell upwards for a value

    On second thought, I'm working on a sheet where, instead of numbers, the first column contains a list of emails. How can I use this formula if the first column contains strings, and not numbers?

  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: Search a cell upwards for a value

    Quote Originally Posted by jmoney2375 View Post
    How can I use this formula if the first column contains strings, and not numbers?
    Maybe this...

    =LOOKUP("zzzzz",A$2:A2)&B2
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Forum Expert
    Join Date
    12-11-2011
    Location
    Netherlands
    MS-Off Ver
    office 365
    Posts
    3,256

    Re: Search a cell upwards for a value

    Maybe something like this?
    Attached Files Attached Files
    Willem
    English is not my native language sorry for errors
    Please correct me if I'm completely wrong

  7. #7
    Registered User
    Join Date
    07-15-2015
    Location
    Cleveland, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Search a cell upwards for a value

    It worked! You guys are awesome.

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

    Re: Search a cell upwards for a value

    You're welcome. We appreciate 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. [SOLVED] Macro to find specific word in cell and then copy and paste the adjacent cell upwards
    By raytej82 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-04-2014, 05:33 PM
  2. Replies: 4
    Last Post: 08-29-2014, 05:41 AM
  3. Insert Numbers Counting From 1 upwards Until It Reaches A Blank Cell Then Start Again
    By TkdKidSnake in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-22-2013, 02:26 PM
  4. want to search upwards in a column on a worksheet
    By wjulaxer13 in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 05-18-2013, 12:34 AM
  5. [SOLVED] Look up non numeric value (upwards)
    By gombi in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 07-31-2012, 08:04 PM
  6. Replies: 3
    Last Post: 04-05-2011, 04:56 AM
  7. Autofill upwards!!!!!
    By Paul Sheppard in forum Excel General
    Replies: 4
    Last Post: 06-01-2006, 09:50 AM

Tags for this Thread

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