+ Reply to Thread
Results 1 to 2 of 2

lookup bottom numerical value using indirect and column lookups?

  1. #1
    Registered User
    Join Date
    12-13-2013
    Location
    Raleigh, NC
    MS-Off Ver
    Excel 2010
    Posts
    12

    lookup bottom numerical value using indirect and column lookups?

    not sure how to phrase this but I'll try to keep simple with an example below (sorry I couldn't figure out the table function). I have an "itemized" worksheets and a summary worksheet.

    Sheet named "ITEMS01":

    APPLES ORANGES BANANAZ
    count1 6
    count2 1
    count3 2 4
    sum 6 3 4

    Summary Sheet:

    ORANGES APPLES BANANAZ
    ITEMS01 3 6 4
    ITEMS02 # # #
    etc. # # #


    So far I'm able to use INDIRECT to make the reference find the item sheet name, also I can have it reference a specific cell in them. The problem comes in because, similar to example above, the columns are not always in the same order and the row where the sum is not always the same (it could be row 3 or row 553)

    Is there some combination of this:

    =INDIRECT("'"&A2&"'!B150")

    and this:

    =INDEX(B:B,MATCH(3E+100,B:B,1))

    where instead of B:B it looks for column titled APPLES then returns the southernmost numerical value in that column?

    Thanks in advance.

  2. #2
    Valued Forum Contributor
    Join Date
    07-10-2017
    Location
    Toronto, Ontario
    MS-Off Ver
    Excel 2013
    Posts
    430

    Re: lookup bottom numerical value using indirect and column lookups?

    You could use this (it will work with named columns too)
    =LOOKUP(2,1/(B:B<>""),B:B)

+ 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. Multivariable bottom-to-top LOOKUP
    By zoefrannie in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-16-2013, 11:21 AM
  2. Match/Index/Lookup - Searching From Bottom to Top (A reverse lookup maybe)
    By Neutralizer in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 05-02-2013, 03:55 AM
  3. Help with lookups, using cell data as a lookup
    By 1.zer0 in forum Excel General
    Replies: 1
    Last Post: 06-23-2010, 07:35 AM
  4. Using a =lookup() for text. no numerical values
    By screamnyak in forum Excel General
    Replies: 2
    Last Post: 03-05-2010, 04:25 PM
  5. Use Lookups to change a value in the lookup table
    By t0m46 in forum Excel General
    Replies: 10
    Last Post: 12-21-2009, 06:07 PM
  6. Using Lookups to change a value in the lookup table
    By t0m46 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 12-11-2009, 07:30 PM
  7. Replies: 1
    Last Post: 04-09-2006, 04:30 PM
  8. Replies: 1
    Last Post: 11-17-2005, 01:55 PM

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