Link to home
Start Free TrialLog in
Avatar of Terry Rogers
Terry RogersFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel VLOOKUP with Multiple Critera

Hello,

I have the following data table:

User generated image
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.

User generated image
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
Avatar of Rgonzo1971
Rgonzo1971

Hi,

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

Open in new window

Regards
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

Avatar of Terry Rogers

ASKER

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?
ASKER CERTIFIED SOLUTION
Avatar of Rgonzo1971
Rgonzo1971

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@ryan You're right
Thank you! Working as expected.