AC Equipment Selector using Index and Match

You should post a workbook with explanations of what you are trying to accomplish: what you want (and why) vs what the formula is actually giving. I'm not sure it will be possible, given all those duplicate values in each column.

protonLeah thanks for your reply.

I have placed an explanation in the spreadsheet of what I am trying to achieve below the criteria. Hope this throws some light on the problem.

Again thanks for your response.

Vince

Originally Posted by v.rodgers

Re-post with updated description.

Can anyone solve this problem?

My first comment would be that the lookup value in Q5 does not appear to exist anywhere in the lookup array.

EDIT: Forget that! I had not seen columns A-C ...

OK - I'll try again!

The combination you are looking for (SDI Triple and 1) does not appear to exist in the lookup table.

Changing SDI to DI in the lookup value cell resolves that part of the problem.

Please clarify:

Should this: "and cells S6 and S7 should show results from cells E13 and E14 respectively"

really be: "and cells S6 and S7 should show results from cells E13 and F13 respectively"?

Because Indoor 3 is column F, not E ...

In S6, you need:

=INDEX(E2:E17,MATCH($Q5,$B$2:$B$17,MATCH($R5,$H$2:$H$17,MATCH($S2,$D$2:$D$17,0))))

and, if I am right, in S7:

=INDEX(F2:F17,MATCH($Q5,$B$2:$B$17,MATCH($R5,$H$2:$H$17,MATCH($S2,$D$2:$D$17,0))))

There are only two rows for the type SDI Triple and none of them have power 1. Moreover if you take criteria in S2 only into consideration then there are 8 rows for SM566BT-E out of which you are interested only in SM1603AT-E (I13), why?
So either you are not applying all the criteria to extract the record or different criteria for different cells. You need to clarify this a bit more.

You are having multiple values meeting the criteria set for Type, Power and Indoor/Outdoor. So I think you need an Array Formula to get the desired values. Since these are Array Formulas so you need to confirm them with Ctrl + Shift + Enter instead of just Enter. (i.e. hold down the Ctrl + Shift and then press Enter)

In S4, try this......

Please Login or Register  to view this content.

In S6, try this......

Please Login or Register  to view this content.

and then drag right (say upto U8) and down to S8.

Is this what you are trying to achieve?

For detail see the attached sheet.

So, is there anything left to help with? What is still not working as you'd anticipate (which bits of the worksheet)?

You are still confusing me: you have highlighted in yellow SDI Twin on the last row of the table, but in your explanation box you mention DI Twin. There is no DI Twin and 3 combination in the lookup table. Please clarify.

If I change R5 to SDI Twin, then row 9 of the sheet is referred to: it has the same combination of lookup criteria as row 17, and as it is first in the array, its results are returned. I think there may be mistakes in the lookup table. Please scrutinise it very carefully for errors before asking for any more help, otherwise we'll end up going round in circles.

Previous answers and discussions are blurring things a bit. Still, it is my impression that you just want an ordinary, multicondition lookup formula. In cases like these I just use one array formula to find the row no and then a simple INDEX using that no to retriev the rest of the data. Am I missing something? I also made a dynamic named range for one of the datavalidation lists and a unique item formula. Not sure if that is relevant but that's how I would do it.

Try these array formulas. (all of them require Ctrl+Shfit+Enter)

In S4

Please Login or Register  to view this content.

In S6

Please Login or Register  to view this content.

In S7

Please Login or Register  to view this content.

Is this what you want?

You're welcome!

---------