+ Reply to Thread
Results 1 to 15 of 15

Vloopkup formula to search from bottom record up.

  1. #1
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Vloopkup formula to search from bottom record up.

    How can I change this formula so it works by searching for the first record match from the bottom most record up (rather than the top down):
    =IFERROR(VLOOKUP($G6, EquipmentData!$B$3:$C$1048576, 2, FALSE),"")

  2. #2
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    One way:
    =LOOKUP(2,1/(EquipmentData!$B$3:$B$100=$G6),EquipmentData!$C$3:$C$100)
    ---------------------------
    Success? Wave it, click on the little star at the bottom left of my responses

  3. #3
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    That formula came up N/A for most records. I need it to reference at least 20,000 rows on that sheet. Could it find the last row, and then work up from that?

  4. #4
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    Just modify the range to suit:
    =LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)
    ---------------------------
    Success now? Wave it, click on the little star at the bottom left of my responses

  5. #5
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    That removed all the N/As but its still referencing the higher records first. I have attached my spreadsheet. The reference sheet is EquipmentData. The last two records both have the same barcode 90909 in the B column. If you go to the ReturnData page and enter barcode 90909 into the G cell in the next empty row, the F column (which contains the formula in question) would return 2 which is the last record in the EquipmentList sheet, however its currently returning 1.
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    As I'm using Excel 2003, I tested it using this modification to suit the max rows in Excel 2003:
    =LOOKUP(2,1/(EquipmentData!$B$3:$B$65536=$G6),EquipmentData!$C$3:$C$65536)
    With 90909 in G6, the formula returned 2, which is the correct return
    Perhaps you could try it again? Do ensure that calculations are set to automatic

  7. #7
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    Thank you, thats strange its working correctly now. However on blank rows (where the is currently no value in G) its displaying 0. How can I change this so it just returns blank "". I tried adding IFERROR but that didn't work.

  8. #8
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    You could use something like this (I don't have IFERROR in Excel 2003):
    =IF(ISERROR(Lookup(...)),"",IF(Lookup(...)=0,"",Lookup(...)))

    Note that this part: .. IF(Lookup(...)=0,"", ..
    is the one which addresses your question:
    > ... However on blank rows (where the is currently no value in G)
    its displaying 0. How can I change this so it just returns blank "" ...
    Note: I presume you meant where col C (ie the return col) was blank

    As Excel returns blank cells as zeros in its calcs, that simple IF should do it for you
    ------------------
    Success? Wave it, click on the little star at the bottom left of my responses
    Last edited by Max, Singapore; 12-25-2013 at 09:23 PM.

  9. #9
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    Sorry, I'm confused as to what the full formula should be now. I tried compiling it but I keep getting errors. What should the entire complete formula be?

  10. #10
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Vloopkup formula to search from bottom record up.

    Are the values you are returning normally text? You can try concatenating a "" to the end of the formula to give you a blank when the reference value is blank - like this:

    =LOOKUP(2,1/(EquipmentData!$B$3:$B$20000=$G6),EquipmentData!$C$3:$C$20000)&""
    Audere est facere

  11. #11
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    Reply to post #9
    Try this expression in say, H6:
    =IF(ISERROR(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)),"",IF(LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)=0,"",LOOKUP(2,1/(EquipmentData!$B$3:$B$1048576=$G6),EquipmentData!$C$3:$C$1048576)))
    Copy down

  12. #12
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    Thank you daddylonglegs and Max. They both worked, however Max that formula seemed to really slow everything down for some reason, but daddylonglegs formula is fine.

  13. #13
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    I just realized its referencing down to B1048576 which is making the code so slow. When I changed daddys code to have the same reference it to went super slow. Im guessing excel is actually checking every row. Is there some way to reference a dynamic named list, or run some formula first to find the last row (by looking for the last row containing data in C) first to prevent this issue?

  14. #14
    Valued Forum Contributor
    Join Date
    09-07-2006
    Posts
    520

    Re: Vloopkup formula to search from bottom record up.

    Some thoughts for post#13 ...
    1. From experience, I'd just go for the smallest range large enough to cover the max possible extent
    2. I'd set the file's calc mode to Manual to manage performance, and recalc only when necessary

  15. #15
    Forum Contributor
    Join Date
    09-16-2013
    Location
    Los Angeles, USA
    MS-Off Ver
    Excel 2011
    Posts
    620

    Re: Vloopkup formula to search from bottom record up.

    Thanks. Im probably going to have to find some alternative, as this spreadsheet will be getting very large.

+ 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] Need VB Routine To Search Top to Bottom Without Restarting at Top
    By geshorse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-30-2012, 12:01 AM
  2. VLOOKUP search from bottom to top ?
    By KASTEO in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 05-26-2012, 02:39 AM
  3. VLOOPKUP returning the value above...
    By massimoo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-13-2010, 07:23 AM
  4. Need to Search from Bottom to Top
    By iturnrocks in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 02-13-2007, 05:09 PM
  5. [SOLVED] Copy and paste from last record at bottom of column
    By [email protected] in forum Excel - New Users/Basics
    Replies: 0
    Last Post: 02-07-2006, 06:30 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