### 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

Join my VBA whatsApp group to know more

13 replies
1. Robert H. Gascon says:

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.

2. Saurav says:

=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)

• Vikas Solanki says:

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

• mail2excelexpert says:

Array Formula {}

ctrl+shift+Enter

• Robert H. Gascon says:

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}))

3. Saurav Gupta says:

=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)

•   says:

You nailed it man. Awesome work

• mail2excelexpert says:

Wrong, I cant find the answer.

• mail2excelexpert says:

It works with Array Formula. {} Brackets
Ctrl+Shift+Enter

Great.