Excel: returning value in range based on 2 criteria

Question

I'm trying to work out the following.

I have the following columns:

What I am trying to do is to create a formula that will return the correct profit based on 2 values:

So, let's say cell A3 = "M3", cell A4 = "Hatchback". How do I create a formula that will return the value in cell F4, 16%?

What seems to break my lookup formulas is the fact that I have multiple identical values (Sedan, Hatchback).

Thank you in advance!

JvdV · Accepted Answer · 2020-10-21 10:52:31Z

1

One possibility:

=INDEX(INDEX(C3:H4,0,MATCH(A3,C2:H2,0)+1),MATCH(A4,INDEX(C3:H4,0,MATCH(A3,C2:H2,0)),0))

answered Oct 21, 2020 at 10:52

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1