Not sure if a nested formula or a Vlookup would work better

jodily

New Member
Joined
Feb 23, 2018
Messages
5
Hi,
I need to create a new column that will show the number of staff per vehicle in order to do further calculations. Generally if they had the same amount of staff regardless if another vehicle showed up to the incident I would use a vLookup table however there are two conditions to the number of staff. If Truck 2 is the only vehicle responding then 4 people are on board however if Car 2 also shows up to this incident then there are only 2 people on Truck 2. Same situation with Ladder 3, if it arrives by itself then 4 people are on board but if it responds with Truck 3 then only 2 people are on board. Below is an example of the data I am working with. I am thinking a combination of an if/and/or equation would work but I can't seem to make it work.
Thank you.
Incident Number
Vehicle # of staff

12-100 Truck 2
12-100
Car 2
12-100
Vehicle 3
12-200
Ladder 3
12-200
Truck 3
12-300
Car 2
12-305
Truck 2
12-305
Car 2
12-305
Vehicle 3
12-400
Ladder 2

<tbody>
</tbody>
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Never use VLOOKUP
i. since the column or row number is hardcoded, if rows/columns are inserted, the whole model blows up.
ii. since the column or row number is hardcoded, you cannot copy down formulas
iii. They use a lot of memory.
iv. If you need to look up more than one data item for that value, you have to look it up each time
v. You can not accurately use the trace dependents function since it appears that cells are being used when they might not be.
vi. It will not work if you ever want to do a look up to the left of the value. Only works on value to the right.
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,915
Members
448,532
Latest member
9Kimo3

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