dutst
Level 1 (4 points)
iWork

Q: Multiple criteria in numbers

I need to search or lookup based on criteria from 2 columns and return the corresponding value from a 3rd column. It seems like such a simple task, yet I cannot find anything that works.

 

There are similar paper names, but different sizes. I want to look up the paper name, and then paper size and have the price returned by the formula.

MacBook Pro (Retina, 15-inch, Mid 2014), iOS 10

Posted on Sep 25, 2016 10:04 PM

by Barry,Solvedanswer
Barry Barry
Level 7 (32,714 points)
iWork
A:

The difficulty with this arrangement is that the lookup functions (including MATCH) can handle only one search value, and return the result from the first instance of that value they find.

 

The example provided above gets around that limitation by placing the data to be retrieved at the intersection of a column and a row, each headed by one of the two chosen specifications.

 

To do the search on the table in your example, it's necessary to add a column where the two (or more) search values are combined to form a single specification that will apply to only one row on the table.

 

Here's an example, with the search value constructed in column K, which may be hidden.

Screen Shot 2016-09-26 at 12.05.42 AM.png

You'll notice that I have placed the lookup function on a separate table. This allows specifying the whole of columns K and J on Table 1 (the top one) as the search range and return value range in the LOOKUP formula below.

 

Table 1 contains a single formula, entered in K2, and filled down to the last row of the table.

 

K2: =B&F

The formula retrieves the values from 'this row' of columns B and F, and concatenates them into a single text string.

 

Table 2 (bottom) contains two formulas:

 

K2: same as the formula at this location on Table 1.

 

I2: =LOOKUP(K2,Table 1::K,Table 1::I)

Gets the text value from K2 on 'this table, looks for it in column K of Table 1, and returns the value from the cell in column J of Table 1 on the row where the formula finds the search value.

 

Both formulas are filled down to the next row, allowing comparison of two choices.

 

Regards,

Barry

Posted on Sep 26, 2016 12:07 AM

Close
dutst

Q: Multiple criteria in numbers

  • All replies
  • Helpful answers

  • by Barry,

    Barry Barry Sep 25, 2016 10:21 PM in response to dutst
    Level 7 (32,714 points)
    iWork
    Sep 25, 2016 10:21 PM in response to dutst

    Hi dutst,

     

    Here's an example from a previous question. The product is different, but the principle is the same.

    Screen Shot 2016-09-25 at 10.10.55 PM.png

    Price lookup is done in Column C of the Main table.

    "Paper" types are listed in the column headers (row 1).

    "Sizes" are listed in column A.

     

    There is one formula, entered in Main::C2, and filled down that column.

     

    C2: =INDEX(Price list::$A$1:$E$7,MATCH(A,Price list::$A,0),MATCH(B2,Price list::$1:$1,0),area-index)

     

    I would suggest using Pop-up Menu cells for the sizes in column A and for the paper names in row 1.

    Cells in the Price list may be formatted as Number or Currency, with two places after the decimal. Main will pick up that formatting for transferred data.

     

    Regards,

    Barry

  • by dutst,

    dutst dutst Sep 25, 2016 10:38 PM in response to Barry
    Level 1 (4 points)
    iWork
    Sep 25, 2016 10:38 PM in response to Barry

    Thanks for the reply Barry. I appreciate the feedback. I will try and see if that helps me figure it out.

     

    I see that I am able to attach an image now, so I have included what I have set up so far. My plan was to use dropdown menu's for B13 and F13, and then have I13 have the value display. Any suggestions would definitely help.

     

    Thanks again!

     

    Screen Shot 2016-09-25 at 11.52.10 PM.png

  • by Barry,Solvedanswer

    Barry Barry Sep 26, 2016 12:07 AM in response to dutst
    Level 7 (32,714 points)
    iWork
    Sep 26, 2016 12:07 AM in response to dutst

    The difficulty with this arrangement is that the lookup functions (including MATCH) can handle only one search value, and return the result from the first instance of that value they find.

     

    The example provided above gets around that limitation by placing the data to be retrieved at the intersection of a column and a row, each headed by one of the two chosen specifications.

     

    To do the search on the table in your example, it's necessary to add a column where the two (or more) search values are combined to form a single specification that will apply to only one row on the table.

     

    Here's an example, with the search value constructed in column K, which may be hidden.

    Screen Shot 2016-09-26 at 12.05.42 AM.png

    You'll notice that I have placed the lookup function on a separate table. This allows specifying the whole of columns K and J on Table 1 (the top one) as the search range and return value range in the LOOKUP formula below.

     

    Table 1 contains a single formula, entered in K2, and filled down to the last row of the table.

     

    K2: =B&F

    The formula retrieves the values from 'this row' of columns B and F, and concatenates them into a single text string.

     

    Table 2 (bottom) contains two formulas:

     

    K2: same as the formula at this location on Table 1.

     

    I2: =LOOKUP(K2,Table 1::K,Table 1::I)

    Gets the text value from K2 on 'this table, looks for it in column K of Table 1, and returns the value from the cell in column J of Table 1 on the row where the formula finds the search value.

     

    Both formulas are filled down to the next row, allowing comparison of two choices.

     

    Regards,

    Barry

  • by dutst,

    dutst dutst Sep 26, 2016 4:34 PM in response to Barry
    Level 1 (4 points)
    iWork
    Sep 26, 2016 4:34 PM in response to Barry

    Thanks Barry, this worked like a charm! That is exactly what I needed!

     

    Thanks so much!!!