1

I'm using Numbers '09 (but can upgrade if need be). How can I find a value using multiple criteria?

Let's say I have a table of flights, with columns for the flight date, flight number (including airline code), booking code, etc.; and another table that contains the earnings in a frequent flyer program (award miles, elite miles, elite dollars) for each airline, date range, and booking code. For each flight (row) in the first table, I need to find the row in the second table that matches the airline code from the flight number, the flight date, and the booking code. I'll then take the value in a specific column of that row and multiply it by the flight distance to get the earnings for that category.

Example:

Table 1 ("Flights") has columns such as:

"Flights"

Table 2 ("Per-Airline") has columns such as:

"Per-Airline"

For the "Projected EQM" column for a flight (e.g., flight in row 3, the second flight), I want to find the row in Table 2 ("Per-Airline") where:

  • The airline code matches the code at the start of the flight number (e.g., "cx")
  • The "Flight Dates From" column is either blank or no earlier than the flight date
  • The "Flight Dates To" column is either blank or no later than the flight date
  • The "Booking Code" column contains the booking code of the flight

In the example, row 96 matches.

Then, I need to take the value in the "EQM" column of that that row (1.5) and multiply it by the value in the "Flight Miles" column. The result is the "Projected EQM".

How would I do this? I've read through the descriptions of the likely functions (LOOKUP, VLOOKUP, MATCH, INDEX) but can only see how to search using a single criterion.

  • What have you tried, but failed? As it stands, you’re asking for a complete answer without showing your attempts to solve it yourself first. Later versions of Numbers may have more advanced functions than are found in v.09. – IconDaemon Oct 23 '17 at 1:39
  • 1
    I haven't been able to figure out how to do a lookup using more than one criterion. I've read and re-read the documentation for the functions that seem like the ones to use, and can't see a way to do it. Are you saying I can't ask how to do something if I can't figure out how I might do it? I'm not sure what you mean by "a complete answer;" what I need is a technique for looking up a cell using more than one test, if I knew that I think I could work out how to fit that into my specific situation. – jetset Oct 23 '17 at 2:29
2

I've gotten it working now, by extending techniques discussed elsewhere (which uses an extra column to contain an aggregate of the match criteria).

To handle the date ranges, I created an extra lookup table that assigns an arbitrary single value to each date range:

enter image description here

In the per-airline earnings lookup table, I added an extra column to hold the Date-Code for the date range, using VLOOKUP against the date-from column, with exact-match, since it finds the largest value that's smaller than the criterion:

enter image description here

And another extra column to hold a calculated lookup-string that's a concatenation of the airline, the booking codes, and the date range code:

enter image description here

Then, in the main table (that holds the flights taken), I added an extra column to contain the lookup string, which is a concatenation of the airline, the date range code only if applicable, and the booking code with asterisks on either side as wild cards, and another extra column to hold the calculated row of the per-airline lookup table:

enter image description here

(Of course, I could have avoided adding two extra columns in both tables by making the formulas more complex, but I opted for better readability.)

Then, the actual earnings columns in the main table uses the LOOKUP function with the calculated per-airline lookup table row and the applicable column, e.g.,:

= IF ( $PNR '1' = "", "",  ROUNDUP ( IF ( INDEX ( 'Per-Airline',  $Lookup Row '1', COLUMN ( 'RDM per $ (EXP)' ) ) > 0, INDEX ( 'Per-Airline',  $Lookup Row '1', COLUMN ( 'RDM per $ (EXP)' ) ) * 

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .