thanksamillion101
New Member
- Joined
- Jul 8, 2020
- Messages
- 41
- Office Version
- 2010
Hello and thank you in advance!! I have a spreadsheet that I use for reporting fuel use for each employee and vehicle. I am needing a formula to return the Vehicle ID (M2) number by matching Dept and Product and the Current Odometer. The formulas I have been using will return the Vehicle ID number, but will not match the Product if the Odometer number is overlapping, it will return the first find by Odometer only and not look for Product to return correct Vehicle ID. I have tried IFERROR by section- Ex. A2:E6 then A7:E13 then A14:E21...I went to Row 21 as future vehicles to be added...hope I am being descriptive enough!
IFERROR(LOOKUP(4,1/($C$2:$C$21<=$J2)/($D$2:$D21>=$J2)/(MATCH("*"&$H2&"*",$B$2:$B$21,0))/(MATCH("*"&$K2&"*",$A$2:$A$21,0)),$E$2:$E$21),"Not Found"))
IFERROR(LOOKUP(4,1/($C$2:$C$21<=$J2)/($D$2:$D21>=$J2)/(MATCH("*"&$H2&"*",$B$2:$B$21,0))/(MATCH("*"&$K2&"*",$A$2:$A$21,0)),$E$2:$E$21),"Not Found"))
Product | Dept | Beg Odom | End Odom | Veh ID | EMP Number | Department | Trans Date | Current Odometer | Product | Veh ID | ||
"DSL","DS+" | Tech, Owner | 162743 | 165945 | 1 | 5555 | Service | 3/1/2019 | 253303 | DS+ | |||
"DSL","DS+" | Tech Admin | 256566 | 266467 | 2 | ||||||||
"DSL","DS+" | Sales, Owner | 209218 | 256354 | 3 | ||||||||
"DSL","DS+" | Tech, Owner | 291711 | 341752 | 4 | ||||||||
"DSL","DS+" | Tech Admin | 342432 | 399487 | 5 | ||||||||
"DSL","DS+" | Tech Admin | 78535 | 128912 | 6 | ||||||||
"DSL","DS+" | Tech Admin | 290000 | 296985 | 7 | ||||||||
"DSL","DS+" | Tech Admin | 360000 | 366374 | 8 | ||||||||
"UNL", "DSL", "DS+" | Tech Admin | 68505 | 211509 | 9 | ||||||||
"UNL", "PREM", "SUP" | Tech Admin | 135936 | 147289 | 10 | ||||||||
"UNL", "PREM", "SUP" | Sales | 183000 | 196667 | 11 | ||||||||
"UNL", "PREM", "SUP" | Tech | 240000 | 255466 | 12 | ||||||||
"UNL", "PREM", "SUP" | Owner,Tech | 330000 | 355374 | 13 | ||||||||
"UNL", "DSL", "DS+" | Tech Admin | 3 | 21 | Equip | ||||||||
"UNL", "DSL", "DS+" | Tech Admin | 23 | 89 | Equip | ||||||||
"UNL", "DSL", "DS+" | Tech Admin | 99 | 99 | Equip | ||||||||
"UNL", "PREM", "SUP" | Tech Admin | 61000 | 66074 | Pers | ||||||||
"UNL", "PREM", "SUP" | Tech, Admin, Sales, Owner | 123456 | 123456 | Equip | ||||||||