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

[jodily]

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.
[TABLE="width: 231"]
<tbody>[TR]
[TD]Incident Number[/TD]
[TD]
Vehicle # of staff

[/TD]
[/TR]
[TR]
[TD]12-100 [/TD]
[TD]Truck 2
[/TD]
[/TR]
[TR]
[TD]12-100
[/TD]
[TD]Car 2
[/TD]
[/TR]
[TR]
[TD]12-100
[/TD]
[TD]Vehicle 3
[/TD]
[/TR]
[TR]
[TD]12-200
[/TD]
[TD]Ladder 3
[/TD]
[/TR]
[TR]
[TD]12-200
[/TD]
[TD]Truck 3
[/TD]
[/TR]
[TR]
[TD]12-300
[/TD]
[TD]Car 2
[/TD]
[/TR]
[TR]
[TD]12-305
[/TD]
[TD]Truck 2
[/TD]
[/TR]
[TR]
[TD]12-305
[/TD]
[TD]Car 2
[/TD]
[/TR]
[TR]
[TD]12-305
[/TD]
[TD]Vehicle 3
[/TD]
[/TR]
[TR]
[TD]12-400
[/TD]
[TD]Ladder 2
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Roncondor]

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.

 

[jodily]

Thank you. Any suggestions for a formula?