Index, Match Help

thanksamillion101

New Member
Joined
Jul 8, 2020
Messages
41
Office Version
  1. 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"))

ProductDeptBeg OdomEnd OdomVeh IDEMP NumberDepartmentTrans DateCurrent OdometerProductVeh ID
"DSL","DS+"Tech, Owner
162743​
165945​
1​
5555Service3/1/2019253303DS+
"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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
How about
+Fluff v2.xlsm
ABCDEFGHIJKLM
1ProductDeptBeg OdomEnd OdomVeh IDEMP NumberDepartmentTrans DateCurrent OdometerProductVeh ID
2"DSL","DS+"Tech, Owner16274316594515555Sales03/01/2019253303DS+3
3"DSL","DS+"Tech Admin25656626646725555Tech03/01/2019290015DS+7
4"DSL","DS+"Sales, Owner20921825635435555Owner03/01/2019341234UNL13
5"DSL","DS+"Tech, Owner2917113417524
6"DSL","DS+"Tech Admin3424323994875
7"DSL","DS+"Tech Admin785351289126
8"DSL","DS+"Tech Admin2900002969857
9"DSL","DS+"Tech Admin3600003663748
10"UNL", "DSL", "DS+"Tech Admin685052115099
11"UNL", "PREM", "SUP"Tech Admin13593614728910
12"UNL", "PREM", "SUP"Sales18300019666711
13"UNL", "PREM", "SUP"Tech24000025546612
14"UNL", "PREM", "SUP"Owner,Tech33000035537413
15"UNL", "DSL", "DS+"Tech Admin321Equip
16"UNL", "DSL", "DS+"Tech Admin2389Equip
17"UNL", "DSL", "DS+"Tech Admin9999Equip
18"UNL", "PREM", "SUP"Tech Admin6100066074Pers
19"UNL", "PREM", "SUP"Tech, Admin, Sales, Owner123456123456Equip
20
Sheet3
Cell Formulas
RangeFormula
M2:M4M2=IFERROR(INDEX($E$2:$E$20,AGGREGATE(15,6,(ROW($E$2:$E$20)-ROW($E$2)+1)/(ISNUMBER(SEARCH(K2,$A$2:$A$20)))/(ISNUMBER(SEARCH(H2,$B$2:$B$20)))/(J2>=$C$2:$C$20)/(J2<=$D$2:$D$20),1)),"Not found")
 
Upvote 0
Solution
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top