AudyAnalyst
New Member
- Joined
- Nov 24, 2021
- Messages
- 20
- Office Version
- 365
- Platform
- Windows
Something Has gone wrong. I was hoping to have a logic lookup. If the location "Seattle" and if the Stage is "2" then lookup in the corresponding column "Seattle", for the % that is associated with "2". This needs to be the same value multiple times. What is the best formula for this? I have tried, XLOOKUP, VLOOKUP, & LOOKUP. with an error every time and I don't know why.
Status
Project List
/XR][/RANGE]
Status
| Seattle | Salt Lake | |
| Stage | Complete | |
1 | 0.50% | 0.50% |
1.5 | 10.40% | 12.50% |
2 | 40.00% | 40.00% |
2.5 | 45.60% | 45.00% |
3 | 50.00% | 50.00% |
3.5 | 61.50% | 60.00% |
4 | 75.00% | 75.50% |
4.5 | 85.50% | 80.00% |
5 | 99.90% | 99.90% |
| Project | Location | Stage | Completeness Rating |
| Blue 1 | Salt Lake | 1 | #N/A |
| Blue 6 | Salt Lake | 1.5 | #N/A |
| Blue 7 | Salt Lake | 1 | #N/A |
| Red 1 | Seattle | 1 | #N/A |
| Red 2 | Seattle | 1 | #N/A |
| Blue 2 | Salt Lake | 2 | 40.00% |
| Red 7 | Seattle | 2 | 40.00% |
| Red 8 | Seattle | 2.5 | 45.60% |
| Blue 3 | Salt Lake | 3 | 50.00% |
| Blue 9 | Salt Lake | 3 | 50.00% |
| Red 3 | Seattle | 3 | 50.00% |
| Red 9 | Seattle | 3 | 50.00% |
| Red 4 | Seattle | 3.5 | 61.50% |
| Blue 4 | Salt Lake | 4 | 75.50% |
| Blue 7 | Salt Lake | 4 | 75.50% |
| Red 5 | Seattle | 4 | 75.00% |
| Red 6 | Seattle | 4 | 75.00% |
| Blue 8 | Salt Lake | 4.5 | 80.00% |
| Blue 5 | Salt Lake | 5 | 99.90% |
| Help Vlookup.xlsx | |||||
|---|---|---|---|---|---|
| A | B | C | |||
| 1 | Seattle | Salt Lake | |||
| 2 | Stage | Complete | |||
| 3 | 1 | 0.50% | 0.50% | ||
| 4 | 1.5 | 10.40% | 12.50% | ||
| 5 | 2 | 40.00% | 40.00% | ||
| 6 | 2.5 | 45.60% | 45.00% | ||
| 7 | 3 | 50.00% | 50.00% | ||
| 8 | 3.5 | 61.50% | 60.00% | ||
| 9 | 4 | 75.00% | 75.50% | ||
| 10 | 4.5 | 85.50% | 80.00% | ||
| 11 | 5 | 99.90% | 99.90% | ||
Status | |||||
| Help Vlookup.xlsx | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Project | Location | Stage | Completeness Rating | ||
| 2 | Blue 1 | Salt Lake | 1 | #N/A | ||
| 3 | Blue 6 | Salt Lake | 1.5 | #N/A | ||
| 4 | Blue 7 | Salt Lake | 1 | #N/A | ||
| 5 | Red 1 | Seattle | 1 | #N/A | ||
| 6 | Red 2 | Seattle | 1 | #N/A | ||
| 7 | Blue 2 | Salt Lake | 2 | 40.00% | ||
| 8 | Red 7 | Seattle | 2 | 40.00% | ||
| 9 | Red 8 | Seattle | 2.5 | 45.60% | ||
| 10 | Blue 3 | Salt Lake | 3 | 50.00% | ||
| 11 | Blue 9 | Salt Lake | 3 | 50.00% | ||
| 12 | Red 3 | Seattle | 3 | 50.00% | ||
| 13 | Red 9 | Seattle | 3 | 50.00% | ||
| 14 | Red 4 | Seattle | 3.5 | 61.50% | ||
| 15 | Blue 4 | Salt Lake | 4 | 75.50% | ||
| 16 | Blue 7 | Salt Lake | 4 | 75.50% | ||
| 17 | Red 5 | Seattle | 4 | 75.00% | ||
| 18 | Red 6 | Seattle | 4 | 75.00% | ||
| 19 | Blue 8 | Salt Lake | 4.5 | 80.00% | ||
| 20 | Blue 5 | Salt Lake | 5 | 99.90% | ||
Project List | ||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| D2:D20 | D2 | =IFS(B2="Seattle",XLOOKUP(C2,Status!$A$5:$A$13,Status!$B$5:$B$13),B2="Salt Lake",XLOOKUP(C2,Status!$A$5:$A$13,Status!$C$5:$C$13)) |
