Excel VLOOKUP with Multiple Critera

Terry Rogers
Terry Rogers used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
Rgonzo1971
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
Ejgil Hedegaard

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 Technical Architect

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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Find out now!
Top Expert 2016
Commented:
then try 
=INDEX(SampleData[Max iOS],MATCH(1,--(IFERROR(SEARCH(C3,SampleData[Description]),0)*(SampleData[Generation])*(SampleData[Released]=C5)>0),0))

Open in new window

Ryan ChongSoftware Team Lead
Commented:
should it be this instead?

Array formula:

=INDEX(SampleData[Max iOS],MATCH(1,--(IFERROR(SEARCH(C3,SampleData[Description]),0)*(SampleData[Generation]=C4)*(SampleData[Released]=C5)>0),0))

?
Rgonzo1971
Top Expert 2016

Commented:
@ryan You're right
Terry RogersIT Technical Architect

Author

Commented:
Thank you! Working as expected.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial