asked on

vlookup with if or where clause

I have a vlookup that I am trying to make it a little more functional:

=VLOOKUP(Q2,IP!A:BA,2,FALSE)

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.

COLUMNa COLUMNb COLUMNc
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

Thanks

Rgonzo1971

Hi,

pls try as Array formula Ctrl-Shift-Enter

=INDEX(B1:B9,MATCH(1,(A1:A9=2)*(C1:C9="Last")))

Regards

ASKER CERTIFIED SOLUTION

Glenn Ray

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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:
=IFERROR(INDEX(IP!A:B,MATCH(Q2,IP!A:A,0)+COUNTIF(IP!A:A,Q2)-1,2),"")

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

-Glenn
EE-Q28500856.xlsx

farmingtonis

ASKER

This one did the trick. THank you

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!

-Glenn