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

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

NamePositionRegion
Isabel BlueManagerLondon
Clinton CorkManager

Manchester

Redd JacksonCrew LeaderManchester
Caleb CookCrew LeaderLondon
Darren DykesManagerBristol
Lucy LoopCrew LeaderBristol


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

RegionName 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 Mar 11, 2018 12:15 PM

Reply
Question marked as Best reply

Posted on Mar 11, 2018 4:24 PM

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.

User uploaded file

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

2 replies

There are no replies.

Looking up text based on multiple criteria

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple ID.