vlookup with if or where clause

Posted on 2014-08-19
Last Modified: 2014-08-20
I have a vlookup that I am trying to make it a little more functional:


This pulls back some of the data that i need.  

However, i want it to be more exact.  I need it to only pull back the data if the data in another column in sheet IP contains the word 'last'

so basically i need something like this:

The table example is below.  Of course, with my simple code about I just pull back a 1 every time ColumnA has a 2.  However, I only want to pull back the match where the first match happens.

2                       1
2                       1,2
2                       1,2,3
2                       1,2,3,4            Last   <-------i just want this one to populate when there is a 2 in my vlookup formula
3                       a
3                       a,b
3                       a,b,c                Last   <-------i just want this one to populate when there is a 3 in my vlookup formula

Question by:farmingtonis
    LVL 47

    Expert Comment


    pls try  as Array formula Ctrl-Shift-Enter


    LVL 27

    Accepted Solution

    I suspect that this can be simplified, but I tested it and it works:

    Note:  The word "Last" must be in column C and the formula doesn't care if "Last" actually is on the final row of a section of data.

    See attached example file.  

    LVL 27

    Expert Comment

    by:Glenn Ray
    IF you always want the final value in the IP sheet for any specific value in column A - regardless of if the word "Last" is there - this formula is much simpler:

    I've updated the example file with both formulas for comparison.


    Author Closing Comment

    This one did the trick.  THank you
    LVL 27

    Expert Comment

    by:Glenn Ray
    You're welcome.  Of course, that second formula would mean that you wouldn't even need the "Last" indicator column, but I'm glad it works!


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Suggested Solutions

    A2 = A1 That kind of cell reference is relative.  If you copy it from A2 to B2, then B2 will get this: B2 = B1 That's all fine and good, but if you then insert a new row above row 2, you'll find: A3 = A1 B3 = B1 This is intentional. …
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now