### Excel Formula Challenge #4

This one is quite easy in comparison to the other formula challenges here. By using excel native function you need to get the price of any selected brand. Be the part of my Excel VBA WhatsApp group to see the solution and challenges like this

What is your formula solution? share it with people in the commentbox. You can download the test file from here

FormulaChallenge4-1.xlsx (71 downloads)### Leave a Reply

Want to join the discussion?Feel free to contribute!

The array formula can be avoided by wrapping the lookup array argument of the second MATCH function with the INDEX function, as follows:

=INDEX($B$5:$Q$12,

MATCH(E14,$B$5:$B$12,0),

MATCH(E15,

INDEX(VLOOKUP(E14,$B$5:$Q$12, {2,5,8,11,14},0),1,0),

1)*3+1)

Row 4 was excluded from the range.

Great

=INDEX($B$4:$Q$12,MATCH(E14,$B$4:$B$12,0),MATCH(E15, VLOOKUP(E14,$B$4:$Q$12, {2,5,8,11,14},0),1)*3+1)

Can you please explain the last Match used in the formula?

Array Formula {}

ctrl+shift+Enter

My non-array formula with only 3 functions is this:

=LOOKUP(E15,

VLOOKUP(E14,B5:Q12,{2,5,8,11,14}),

VLOOKUP(E14,B5:Q12,{4,7,10,13,16}))

Awesome job!

=INDEX($B$4:$Q$12,MATCH(E14,$B$4:$B$12,0),MATCH(E15, VLOOKUP(E14,$B$4:$Q$12, {2,5,8,11,14},0),1)*3+1)

You nailed it man. Awesome work

Wrong, I cant find the answer.

Did you enter as array formula?

It works with Array Formula. {} Brackets

Ctrl+Shift+Enter

Great.

=INDEX(rngData,MATCH($E$14,rngCol,0),MATCH($E$15,OFFSET(INDEX(rngData,MATCH($E$14,rngCol,0),1),,,,COUNTA(rngRow)),1)+MATCH($C$16,OFFSET(INDIRECT(ADDRESS(ROW($B$4),MATCH($E$15,OFFSET(INDEX(rngData,MATCH($E$14,rngCol,0),1),,,,COUNTA(rngRow)),0)+COLUMN(B4))),,,,COUNTA(rngRow)),0))

With CSE