We help IT Professionals succeed at work.

Excel VLOOKUP with Multiple Critera

169 Views
Last Modified: 2018-02-15
Hello,

I have the following data table:

Data Table
I need to lookup the Max iOS value based upon the generation lookup value and the lowest release lookup value and where the description contains the lookup value.

Generation Lookup is already calculated, along with the lowest release date.

Lookup Values
I have attached my Excel file for further help.

I expect I will need an array formula, but am not "expert" enough to work out how to build it.

Any help would be much appreciated! :)
Simplified.xlsx
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2016

Commented:
Hi,

pls try as Array formula
=INDEX(SampleData[Max iOS],MATCH(C4&C5,SampleData[Generation]&SampleData[Released],0))

Open in new window

Regards
CERTIFIED EXPERT

Commented:
Add Index to the Match range in Rgonzo's formula, and it can be a regular formula
=INDEX(SampleData[Max iOS],MATCH(C4&C5,INDEX(SampleData[Generation]&SampleData[Released],,),0))

Open in new window

Terry RogersIT Senior Engineer

Author

Commented:
Thank you. This doesn't seem to include the "must contain" the lookup value (ipad air).

This is needed because there could be multiple product types released on the same day with the same generation number, but with different max is versions.

How would we add this criteria to the formula?
CERTIFIED EXPERT
Top Expert 2016
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT
Top Expert 2016

Commented:
@ryan You're right
Terry RogersIT Senior Engineer

Author

Commented:
Thank you! Working as expected.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions