Spreadsheet: 46781d1245725319-lookup-or-vlookup-within-cse-array-2007-for-excel-forum-solved
Sheet: Data Table

 |  | 4. Solved!! The trick was to create another criterion array that incorporated the Match function (type 0) to ensure that any numeric answer was calculated from exact matches with route data in the Shipping  table. You can test by adding ch-euro and us-euro routes to the [NAME] table; the transaction 2 orange cell will turn from #N/A to 24.1 .  Wonderful!
 |  |  |  |  |  |  |  |  | 3. Time To Try VLOOKUP
Table=
"Transactions" |  |  |  |  |  |  |  |  | In the F column, I try a VLOOKUP version of the formula used in the E |  |  |  |  |  | Table="Shipping"
 |  |  |  |  |  |  |  |  | column.  Because I request an exact match, I hope that this formula will
[NAME] | Wk | From | To-Country | Sum Of Alt Routes (LOOKUP method) | Sum Of Alt Routes (VLOOKUP method) | This formula works!!!  (MATCH + LOOKUP) |  |  | alert me if route data is missing from the [NAME] table.  Unfortunately, I cannot explain the results.  So now I am stuck.  Can you help me?    I am looking for a formula that does not require new rows or columns, |  |  |  |  |  | Route | K-km
1 | 2008-01-10 00:00:00 | ch | asia |  |  |  |  |  | other than those shown here. And it should alert me in some way |  |  |  |  |  | ch-asia | 2.655768
2 | 2008-01-10 00:00:00 | [NAME] | euro |  |  |  |  |  | (ex: #N/A) if route data is missing from the [NAME] table. Thanks! |  |  |  |  |  | ch-br | 19.068192
3 | 2008-01-10 00:00:00 | us | lam |  |  |  |  |  |  |  |  |  |  |  | [NAME] | 16.855052
 |  |  |  |  |  |  |  | ^^^^^ |  |  |  |  |  |  | [NAME] | 1.54642
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] | 10.098956
 | 1. A Cell That Works Perfectly: The Yellow Cell |  |  |  |  |  | >>> |  | 2. A Cell That Doesn't Work: The Pink Cell |  |  |  |  |  | tu-ireland | 4.00032
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | tu-it | 0.8593280000000001
 | The yellow cell works perfectly.  It asks: |  |  |  |  |  |  |  | The same formula in the pink cell doesn't meet my needs. It asks: |  |  |  |  |  | tu-lam | 0
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  | us-asia | 13.764064
 | Q: Who were the other suppliers -- other than ch -- on 1/10/2008? |  |  |  |  |  |  |  | Q: Who were the other suppliers -- other than [NAME] -- on 1/10/2008? |  |  |  |  |  | us-br | 9.009979999999999
 | A: [NAME] and us |  |  |  |  |  |  |  | A: ch and us |  |  |  |  |  | us-lam | 2.189064
 | Q: What is the distance from [NAME] to asia? From us to asia? |  |  |  |  |  |  |  | Q: What is the distance from ch to euro? From us to euro?
 | A: 16.9 and 13.8 |  |  |  |  |  |  |  | A: Clunk ! [NAME]! Because neither route is listed in the Shipping table, |  |  |  |  |  | ^^^^^^^ | ^^^^^^^
 |  |  |  |  |  |  |  |  | the next "lowest" entries are used.  This results in adding the ch-br |  |  |  |  |  | Test data to add to Shipping table:
 | Q: Add them together |  |  |  |  |  |  |  | and us-br distances, yielding 28.1. For my purposes, this result is |  |  |  |  |  | ch-euro | 13
 | A: 30.6 |  |  |  |  |  |  |  | a random number. |  |  |  |  |  | us-euro | 5
 | CORRECT!! |  |  |  |  |  |  |  | Bigger problem:  aside from being "random", the 28.1 result does not
 |  |  |  |  |  |  |  |  | warn me that the required route information needed was not listed in the
 |  |  |  |  |  |  |  |  | Shipping table.  Thus, I can't tell which of my results are valid (yellow)
 |  |  |  |  |  |  |  |  | or invalid (pink).