[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1053
  • Last Modified:

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
0
farmingtonis
Asked:
farmingtonis
  • 3
1 Solution
 
Rgonzo1971Commented:
Hi,

pls try  as Array formula Ctrl-Shift-Enter

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

Regards
0
 
Glenn RayExcel VBA DeveloperCommented:
I suspect that this can be simplified, but I tested it and it works:
=IFERROR(INDEX(IP!A:B,MATCH(Q2,IP!A:A,0)+MATCH("Last",OFFSET(IP!$C$1,MATCH(Q2,IP!A:A,0)-1,,COUNTIF(IP!A:A,Q2),),0)-1,2),"")

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.  

Regards,
-Glenn
EE-Q28500856.xlsx
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0
 
farmingtonisAuthor Commented:
This one did the trick.  THank you
0
 
Glenn RayExcel VBA DeveloperCommented:
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
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now