Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Question:

# Question:Q:Looking up text based on multiple criteria

Hi

I'm pretty new to Numbers and would really appreciate your help with something that's probably fairly straightforward but not anything I've been able to work out for myself yet...

In Sheet 1 I have an ever changing list of personnel like this:

Sheet 1

Name Position Region
Isabel Blue Manager London
Clinton Cork Manager

Manchester

Darren Dykes Manager Bristol

In Sheet 2 I would like to automatically lookup the name of the Manager for each region in column 2.

Region Name of Manager
Bristol
Leeds
London
Manchester

I've tried using LOOKUP but because I need the result to be based on the Position and Region I've not got it to work yet. Your suggestions will be gratefully received!

Thank you.

Rob

Numbers 4.3.1

Posted on

Question marked as Solved

Hi b or b,

LOOKUP (and MATCH, which may be a better tool for this job) require a single search-value. Your easiest route here is to add a 'search key' column to the data table.

Cell in column D of Data contain this formula, entered in D2, then filled to the bottom of that column:

D2: C2&B2

The formula joins the Region name and Position name into a single string. The column should be hidden, as sit is filled automatically, and is needed only by the formula below.

There are two versions of the "Managers" table. The first contains the formula shown below the tables, entered in B2 and filled to the bottom row of that column.

B2: INDEX(Data::A,MATCH(A2&B\$1,Data::D,0))

A2&B\$1 created a search-value by joining the Region name in 'this row' of column A and the Position name in Row 1 of column B.

MATCH searches for this value in column D of Data, and is set (0) to require an exact match. MATCH returns a number indicating the position in the list in column D holding the search value.

INDEX gets the number from Match, and returns the value in the same position (row) of Column A of Data.

Where MATCH cannot find an exact match for the search term, it returns an error, and the cell is flagged with a red error triangle. To prevent that, the same formula, in column B of Managers-1, is edited to include an error trap:

B2: IFERROR(INDEX(Data::A,MATCH(A2&B\$1,Data::D,0)),"N/A")

If an error occurs, the formula returns whatever is placed in the bold part after the core formula.

If the core formula does not throw an error, the core formula's result is returned.

If your preference is to have the Leeds line show a 'blank' cell in the Manager column, change "N/A" to ""

NOTE: "Ever changing lists of personnel" and other data are contained in Tables. Sheets, in Numbers are 'large empty canvasses, on which may be placed Tables. Charts, Images, Shapes and other Objects.

Every Sheet contains only a single Table when it is created. By default, that Table is named "Table 1" Editing the table name to a distinct name (different from any other Table name in the document) that reflects it's purpose makes formulas more readable, and shorter, as Sheet names are no longer needed as part of the cell address.

Regards,

Barry

Posted on

Question marked as Solved

Hi b or b,

LOOKUP (and MATCH, which may be a better tool for this job) require a single search-value. Your easiest route here is to add a 'search key' column to the data table.

Cell in column D of Data contain this formula, entered in D2, then filled to the bottom of that column:

D2: C2&B2

The formula joins the Region name and Position name into a single string. The column should be hidden, as sit is filled automatically, and is needed only by the formula below.

There are two versions of the "Managers" table. The first contains the formula shown below the tables, entered in B2 and filled to the bottom row of that column.

B2: INDEX(Data::A,MATCH(A2&B\$1,Data::D,0))

A2&B\$1 created a search-value by joining the Region name in 'this row' of column A and the Position name in Row 1 of column B.

MATCH searches for this value in column D of Data, and is set (0) to require an exact match. MATCH returns a number indicating the position in the list in column D holding the search value.

INDEX gets the number from Match, and returns the value in the same position (row) of Column A of Data.

Where MATCH cannot find an exact match for the search term, it returns an error, and the cell is flagged with a red error triangle. To prevent that, the same formula, in column B of Managers-1, is edited to include an error trap:

B2: IFERROR(INDEX(Data::A,MATCH(A2&B\$1,Data::D,0)),"N/A")

If an error occurs, the formula returns whatever is placed in the bold part after the core formula.

If the core formula does not throw an error, the core formula's result is returned.

If your preference is to have the Leeds line show a 'blank' cell in the Manager column, change "N/A" to ""

NOTE: "Ever changing lists of personnel" and other data are contained in Tables. Sheets, in Numbers are 'large empty canvasses, on which may be placed Tables. Charts, Images, Shapes and other Objects.

Every Sheet contains only a single Table when it is created. By default, that Table is named "Table 1" Editing the table name to a distinct name (different from any other Table name in the document) that reflects it's purpose makes formulas more readable, and shorter, as Sheet names are no longer needed as part of the cell address.

Regards,

Barry

Mar 11, 2018 4:24 PM

Hi Barry

Really appreciate you taking the time to explain that. A lot more complicated than I imagined!

Thanks very much!

Rob

Mar 12, 2018 12:06 PM

User profile for user: blueorblind

Question: Looking up text based on multiple criteria