Numbers for Mac: Index using 3 or more criteria

Hi Ryan,

Since you have row numbers down the left you can use SUMIFS (or AVERAGEIFS) to "calculate" the right row number from multiple criteria in a formula, like this:

In B9: =SUMIFS(Hardware List::A,Hardware List::F,B2,Hardware List::G,B3,Hardware List::H,B4,Hardware List::I,B5)

B10: =MATCH(B7,Hardware List::$2:$2,0)

B11: =INDEX(Hardware List::A$1:R$15,B9,B10,1)

B12: =INDEX(Hardware List::B,B9)

Note that:

--You don't need a concatenation key.

-- You can easily add/remove/change column-condition pairs within SUMIFS as needed if the structure of your Hardware List table changes.

-- You can look up the Part "Number" as shown, but you don't need that number to look up information.

The SUMIFS will come up with the correct result as long as you don't have more than one entry with the exact same attributes. That's because it will be "summing" only one number from column A.

BTW, obviously have to be take care to have exact spelling. The size on Row 7 is misspelled. One way to prevent misspelling is to format the cells as Pop-Up Menu.

SG

I suggest adding a column called "Key" where you concatenate the three fields together.

the key on your case would be something like:

=E2&"_"&F2&"_"&G2&"_"&H2

I suggest making the key column the first column in your table.

then in the search table, enter the selections (hopefully from pop-up menus.

the search results can be obtained using the function vlookup (or lookup + index)

=MATCH(B5,Hardware Price List::Concatenatenation Key,0)

will give you the row number where it finds the exact match. In general it gives you the position of the found item in the range you are searching. You are searching the entire column so it equals the row number. The last parameter (the zero) specifies exact match.

=INDEX(Hardware Price List::Part Number,MATCH(B5,Hardware Price List::Concatenatenation Key,0))

will give you the part number.

You may choose to do individual INDEX(MATCH()) formulas for each column you want data from or you can do one MATCH to get the position and then use that number in several INDEX functions.

The LOOKUP function will return a close match if there is no exact match. You need to use VLOOKUP or use INDEX and MATCH together. VLOOKUP and MATCH allow you to specify exact match only. VLOOKUP requires your concatenated column to be somewhere to the left of the data you want to return. INDEX/MATCH does not require that.

Hi Ryan,

Welcome to Numbers for Mac!

Numbers has some ability with array functions, but Numbers is not a Excel clone.

Some questions:

1. Do you need to label each column in Row 1 of the table "Hardware Price List" with a number? If not, you can delete Row 1.
2. Is every part number available in every finish (Chrome, Brushed Nickel, Polished Nickel, Oil Rubbed Bronze, Brass, SatinBrass, Matte Black, Antique Brass)? If so, your table needs many more rows to hold every combination of Size, Mounting Type, Adjustability, Style, Finish before you can do a lookup or Index-Match. And, yes, Index-Match does work In Numbers if the table to search is designed that way.
3. What is the purpose of the left column? Is that column A in a table?
4. Where are the prices? Are the prices the values in columns 10-17 in your screen shot?

More information on your overall aim will lead to a solution!

Regards,

Ian.