Looking to get formula that pulls information across three columns

[Mwelanetz]

I am need of some assistance. I am looking to create a formula that correlates information to gain a match using three different criteria. Those are First 8 nos. of a VIN, an equipment code (4 DIGITS) and a region #. I have tried several things but can't seem to get it to work. Can anyone help?

 

[texasalynn]

Welcome to MrExcel board....

so are the lookup table and match data in three separate cells?

 

[Aladin Akyurek]

I am need of some assistance. I am looking to create a formula that correlates information to gain a match using three different criteria. Those are First 8 nos. of a VIN, an equipment code (4 DIGITS) and a region #. I have tried several things but can't seem to get it to work. Can anyone help?

Not enough info to go on, but the following might help...

Control+shift+enter, not just enter:

=INDEX(ResultRange,MATCH(1,
     IF(LEFT(VINrange,8)=VINcriterion,
     IF(EquipmentRange=EquipmentCriterion,
     IF(RegionRange=RegionCriterion,1))),0))

 

[Mwelanetz]

The information would be a string of numbers and letters from one, 4 numbers from another and a single digit from the last. The result I am looking for is the amount of vehicles with a certain VIN(8), an equipment code(4) within a certain region. Is this possible? And If it would give me an average $ amount of the sales of the vehicles selected. Thanks!

 

[Aladin Akyurek]

The information would be a string of numbers and letters from one, 4 numbers from another and a single digit from the last. The result I am looking for is the amount of vehicles with a certain VIN(8), an equipment code(4) within a certain region. Is this possible? And If it would give me an average $ amount of the sales of the vehicles selected. Thanks!

And still no info on which ranges are relevant or to be used...

=SUMPRODUCT(
     SumRange,
     --(LEFT(VINrange,8)=VINcriterion),
     --(EquipmentRange=EquipmentCriterion),
     --(RegionRange=RegionCriterion))

This yields a total.


For averaging, control+shift+enter, not just enter...

=AVERAGE(
     IF(LEFT(VINrange,8)=VINcriterion,
     IF(EquipmentRange=EquipmentCriterion,
     IF(RegionRange=RegionCriterion,Range2Average))))

 

[Mwelanetz]

Not sure what you are looking for but maybe this will help.

Columns A2:3245, K2:L3245, & L2:L3245

The vin, region and equip code need to be exact match as criteria for selection. The results would show the Vin and # Match by region. on a seperate page.

 

[Aladin Akyurek]

Not sure what you are looking for but maybe this will help.

Columns A2:3245, K2:L3245, & L2:L3245

The vin, region and equip code need to be exact match as criteria for selection. The results would show the Vin and # Match by region. on a seperate page.

I wished what you are looking for was clear...

=SUMPRODUCT(--(LEFT(A2:A3245,8)=X2),--(K2:K3245=Y2),--(L2:L3245=Z2))

X2 houses a vin of interest, Y2 a region, and Z an equip.

The foregoing yields a count or records satisfying X2, Y2, and Z2 simultaneously.

Is this what you are after?

 

[Mwelanetz]

YES! Absolutely, Thank you very much.

 

[Mwelanetz]

Maybe I spoke too soon. I am getting #value and #N/A errors.

 

[Aladin Akyurek]

Maybe I spoke too soon. I am getting #value and #N/A errors.

What is the exact formula that you have tried?

Would you also post a tiny sample, with emphasis on tiny, and expected results?