# Looking to get formula that pulls information across three columns

#### Mwelanetz

##### New Member
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?

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### texasalynn

##### Well-known Member
Welcome to MrExcel board....

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

##### MrExcel MVP
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:
Code:
``````=INDEX(ResultRange,MATCH(1,
IF(LEFT(VINrange,8)=VINcriterion,
IF(EquipmentRange=EquipmentCriterion,
IF(RegionRange=RegionCriterion,1))),0))``````

#### Mwelanetz

##### New Member
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!

##### MrExcel MVP
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...

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

This yields a total.

For averaging, control+shift+enter, not just enter...
Code:
``````=AVERAGE(
IF(LEFT(VINrange,8)=VINcriterion,
IF(EquipmentRange=EquipmentCriterion,
IF(RegionRange=RegionCriterion,Range2Average))))``````

#### Mwelanetz

##### New Member
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.

##### MrExcel MVP
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

##### New Member
YES! Absolutely, Thank you very much.

#### Mwelanetz

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

##### MrExcel MVP
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?

Replies
7
Views
2K
Replies
1
Views
734
Replies
11
Views
1K
Replies
5
Views
308
Replies
6
Views
341

1,191,165
Messages
5,985,034
Members
439,935
Latest member
Monty238

### 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.

### Which adblocker are you using?

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

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