Using a Lookup Column (follow up question)

Question

Following up this question on using Lookup columns: How to use LookUp column?

How would you make this work looking up values from a calculated field (Store No) rather than manually inputting the value to lookup on? E.g.

Location    Size Supported      Store No (=[Loc.]&[Size])   Store Name  
   A             1                 A1                         StoreA  
   A             2                 A2                         StoreB  
   A             3                 A3                         StoreC  
   B             1                 B1                         StoreD  
   B             2                 B2                         StoreE  
   B             3                 B3                         StoreF

data can be looked up by
Parent List

Order      Location    Size    Store No (=[Location]&[Size])  
  1           A         2             A2  
  2           B         2             B2  
  3           A         1             A1  

---

So that that the Parent List becomes;
Parent List

Order      Location    Size    Store No (=[Loc]&[Size])  Store Name (lookup)
  1           A         2             A2                    Store B
  2           B         2             B2                    Store E
  3           A         1             A1                    Store A

---

(Where Store No is a Calculated Column in both the Parent List and Looked up List)
?

Answer 1

Create two Lists

• first will be stores: Like Table1: Store No, StoreName fields
• second will be like Table3: Order, Item No, Store No (lookup field to the Table1), Store Name (store name will be added when creating LookUp field as additional value)

Follow picture:

1. Select Lookup
2. Select Table1 list
3. Select Store No
4. Check Store Name