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:
Table 2 ("Per-Airline") has columns such as:
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.