I have a report that I want to vlookup the Dept based on the PCAT. This is where it gets complicated...There are multiple lines on each order and I only want it to bring in the Dept on one line of the order and preferably the lowest line number of the order. I had come up with =IF(COUNTIF(A:A,A2)=1,VLOOKUP(C2,J:K,2,FALSE),IF(B2=1,VLOOKUP(C2,J:K,2,FALSE),"")) which almost works but not every order has a line 1 such as order 1909896-00. Do you have any ideas on how this can be done? My end goal is to do a sumif of miles per dept but I don't want to count the # of miles per order multiple times.
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Is there a way to create a vlookup that brings in something only one time per order #?
- Thread starter jloosen
- Start date
shift-del
Well-known Member
- Joined
- Aug 28, 2009
- Messages
- 797
- Office Version
- 365
- Platform
- Windows
Like this?
| Mappe6 | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| A | B | C | D | E | F | G | |||
| 1 | Order | Line | PCATID | Dept | |||||
| 2 | G | 10 | 6042 | ||||||
| 3 | G | 4 | 6000 | ||||||
| 4 | G | 8 | 6010 | ||||||
| 5 | G | 1 | 6010 | Golf Irrig | |||||
| 6 | G | 2 | 6000 | ||||||
| 7 | B | 3 | 6010 | ||||||
| 8 | B | 2 | 5128 | ||||||
| 9 | B | 1 | 9000 | Xmas | |||||
| 10 | W | 4 | 6010 | ||||||
| 11 | W | 7 | 6042 | ||||||
| 12 | W | 9 | 9000 | ||||||
| 13 | W | 3 | 5128 | Res/Com | |||||
Tabelle1 | |||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| G2:G13 | G2 | =IF(A2&MINIFS($B$2:$B$13,$A$2:$A$13,A2)=A2&B2,XLOOKUP(C2,$J$2:$J$6,$K$2:$K$6),"") |
Upvote
0
Solution
That worked. Thank you so much! It was driving me crazy.
Upvote
0
Similar threads
