Can someone help me with my XLOOKUP formula please? I thought I had it laid out correctly, but it seems like it doesn't like my text values I have in the tables. I've looked and read the help section to figure it out, but I am not getting anywhere. Both of these tables here are on two different tabs. What I am trying to do is on the first table (Customer) is select a dropdown of customers and then XLOOKUP would look at the customer chosen, and then change the pricings depending on the customer. The pricing sheet is on another tab/table called job type.
| Standard Pricing Copy.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Customer | |||||
| 2 | ||||||
| 3 | ||||||
| 4 | General Inspection | |||||
| 5 | Inspection Type | Price | Charge Type | |||
| 6 | Misc. Subs - DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 7 | Misc. Subs - DS1 CAT 2 Inspection | #VALUE! | ||||
| 8 | NonMag Connection - DS1 CAT 2 | #N/A | ||||
| 9 | NonMag Sub - DS1 CAT 3-5 | #VALUE! | ||||
| 10 | HWDP - DS1 CAT 2 Inspection | #VALUE! | ||||
| 11 | HWDP - DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 12 | DC - DS1 CAT 2 Inspection | #VALUE! | ||||
| 13 | DC - DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 14 | DP Range II - DS1 CAT 3 Inspection | #VALUE! | ||||
| 15 | DP Range II - DS1 CAT 4 Inspection | #VALUE! | ||||
| 16 | DP Range II - DS1 CAT 4 + Black Light | #VALUE! | ||||
| 17 | DP Range II - DS1 CAT 5 Inspection | #VALUE! | ||||
| 18 | DP Range III - DS1 CAT 3 Inspection | #VALUE! | ||||
| 19 | DP Range III - DS1 CAT 4 Inspection | #VALUE! | ||||
| 20 | DP Range III - DS1 CAT 4 + Black Light | #VALUE! | ||||
| 21 | DP Range III- DS1 CAT 5 Inspection | #VALUE! | ||||
| 22 | Drill Pipe Rig Minimum | #N/A | ||||
| 23 | MPI Per Ft | #VALUE! | ||||
| 24 | LPI Per Ft | #VALUE! | ||||
| 25 | Blacklight Connection | #N/A | ||||
| 26 | Clean/Visual Connection | #N/A | ||||
| 27 | Post Inspeciton - DS1 CAT 2 | #VALUE! | ||||
| 28 | Post Inspection - Field Lathe Reface | #VALUE! | ||||
| 29 | 5" Mud Motor DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 30 | 6 1/2" Mud Motor DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 31 | 8" Mud Motor DS1 CAT 3-5 Inspection | #VALUE! | ||||
| 32 | 8" Mud Motor DS1 CAT 3-5 Inspection - Stators Only | #VALUE! | ||||
| 33 | Mud Motor DS1 CAT 3-5 Inspection | #VALUE! | ||||
Price Sheet | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B6:B33 | B6 | =XLOOKUP(A2,Customer,XLOOKUP([@[Inspection Type]],Customer_Pricing[Job Type],Customer_Pricing[Rig 3])) |
| Named Ranges | ||
|---|---|---|
| Name | Refers To | Cells |
| Customer | ='Customer Price Sheet'!$1:$1 | B6:B33 |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| A2 | List | ='Customer Price Sheet'!$A$303# |
| Standard Pricing Copy.xlsx | ||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | H | I | J | K | L | |||
| 1 | Job Type | Standard Price | Rig 1 | Rig 2 | Rig 3 | Rig 4 | Rig 5 | Rig 6 | Rig 7 | Rig 8 | Rig 9 | Rig 10 | ||
| 2 | Misc. Subs - DS1 CAT 3-5 Inspection | $ 65.00 | $ 65.00 | $ 47.00 | $ 55.00 | $ 65.00 | ||||||||
| 3 | Misc. Subs - DS1 CAT 2 Inspection | $ 23.00 | $ 40.00 | |||||||||||
| 4 | NonMag Sub - DS1 CAT 2 | |||||||||||||
| 5 | NonMag Sub - DS1 CAT 3-5 | |||||||||||||
| 6 | HWDP - DS1 CAT 2 Inspection | $ 18.00 | $ 31.00 | $ 32.00 | $ 40.00 | |||||||||
| 7 | HWDP - DS1 CAT 3-5 Inspection | $ 75.00 | $ 100.00 | $ 85.00 | $ 75.00 | $ 85.00 | $ 52.00 | $ 75.00 | ||||||
| 8 | DC - DS1 CAT 2 Inspection | $ 18.00 | $ 31.00 | $ 16.00 | $ 40.00 | |||||||||
| 9 | DC - DS1 CAT 3-5 Inspection | $ 65.00 | $ 80.00 | $ 68.00 | $ 55.00 | $ 70.00 | ||||||||
| 10 | DP Range II - DS1 CAT 3 Inspection | $ 19.00 | $ 19.00 | $ 15.50 | $ 18.00 | $ 18.00 | $ 13.50 | $ 17.00 | ||||||
| 11 | DP Range II - DS1 CAT 4 Inspection | $ 25.00 | $ 32.00 | $ 25.00 | $ 22.00 | $ 24.00 | $ 24.00 | $ 21.00 | ||||||
| 12 | DP Range II - DS1 CAT 4 + Black Light | $ 49.00 | $ 30.00 | |||||||||||
| 13 | DP Range II - DS1 CAT 5 Inspection | $ 35.00 | $ 40.00 | $ 37.50 | $ 40.00 | |||||||||
| 14 | DP Range III - DS1 CAT 3 Inspection | $ 25.00 | $ 24.00 | $ 17.00 | $ 23.00 | |||||||||
| 15 | DP Range III - DS1 CAT 4 Inspection | $ 31.00 | $ 30.00 | $ 24.00 | $ 29.00 | |||||||||
| 16 | DP Range III - DS1 CAT 4 + Black Light | $ 35.00 | ||||||||||||
| 17 | DP Range III- DS1 CAT 5 Inspection | $ 41.00 | $ 45.00 | $ 39.00 | $ 45.00 | |||||||||
Customer Price Sheet | ||||||||||||||
