VLOOKUP/INDEX multiple criteria

[3345james]

Having brain freeze: Need a formula on Trips Worksheet (Highlighted yellow) that pulls a number from a specified column on another worksheet (Provider Vehicles) PV

I'm thinking it's an IF formula but am scratching my head.

G2 should give me a total from Provider Vehicles (PV) worksheet J:N based on B2 and C2

B2 is found on PV J:J
If c2 is A then total comes from PV K:K
If C2 is W then . . . L:L
If C2 is VS then . . . M:M
If C2 is S then . . . N:N

Thanks
Jim

[MarvinP]

HI Jim,

No formulas needed if you use Pivot Tables. See the attached where I've done a PT for you on the trips sheet. If you remove the blank columns on the PV you could use the county as a filter. See if a Pivot table answer isn't better/easier for you.

PT for PV Sum or Count.xlsx

[3345james]

Thanks MarvinP
Due to other factors (These are part of a much larger data gathering into a singular data base) a pivot table would not be our first choice.
I ended up with this formula:
=IF(C2="A",VLOOKUP(B2,'Provider Vehicles'!$J:$N,2,0),
IF(C2="W",VLOOKUP(B2,'Provider Vehicles'!$J:$N,3,0),
IF(C2="VS",VLOOKUP(B2,'Provider Vehicles'!$J:$N,4,0),
IF(C2="S",VLOOKUP(B2,'Provider Vehicles'!$J:$N,5,0),0))))

Thanks again for your time
Jim

[AlKey]

Hi Jim
Enter this formula in G2 and copy down

Please Login or Register  to view this content.

[3345james]

I like this approach
Nice use of Vllokup and Match
MATCH is not quite in my wheelhouse yet.

Thanks Again