Multiple column VLOOKUP

[makinmomb]

I have this on a to o columns

[TABLE="width: 1056"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]T456DJQ[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4144[/TD]
[TD]T637DFR[/TD]
[TD="align: right"]28.664[/TD]
[TD="align: right"]2149.8[/TD]
[TD]T293DCM[/TD]
[TD="align: right"]27.302[/TD]
[TD="align: right"]1775[/TD]
[TD]T674BUX[/TD]
[TD="align: right"]28.509[/TD]
[TD="align: right"]3278.535[/TD]
[TD]T452DJR[/TD]
[TD="align: right"]27.074[/TD]
[TD="align: right"]2572.03[/TD]
[/TR]
[TR]
[TD]T785DDS[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4144[/TD]
[TD]T472BLQ[/TD]
[TD="align: right"]27.935[/TD]
[TD="align: right"]2095.125[/TD]
[TD]T406CZS[/TD]
[TD="align: right"]27.346[/TD]
[TD="align: right"]747[/TD]
[TD]T137BHX[/TD]
[TD="align: right"]28.453[/TD]
[TD="align: right"]3272.095[/TD]
[TD]T706CWY[/TD]
[TD="align: right"]26.955[/TD]
[TD="align: right"]2560.725[/TD]
[/TR]
[TR]
[TD]T468DDA[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4144[/TD]
[TD]T532AWW[/TD]
[TD="align: right"]27.434[/TD]
[TD="align: right"]2057.55[/TD]
[TD]BAE6520[/TD]
[TD="align: right"]27.283[/TD]
[TD="align: right"]746[/TD]
[TD]T690BKE[/TD]
[TD="align: right"]28.456[/TD]
[TD="align: right"]3272.44[/TD]
[TD]T229BKK[/TD]
[TD="align: right"]27.121[/TD]
[TD="align: right"]2576.495[/TD]
[/TR]
[TR]
[TD]T960BCA[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4144[/TD]
[TD]T245DEZ[/TD]
[TD="align: right"]27.491[/TD]
[TD="align: right"]2061.825[/TD]
[TD]T442BJS[/TD]
[TD="align: right"]26.652[/TD]
[TD="align: right"]727[/TD]
[TD]T316CBX[/TD]
[TD="align: right"]28.516[/TD]
[TD="align: right"]3279.34[/TD]
[TD]T699BFJ[/TD]
[TD="align: right"]26.946[/TD]
[TD="align: right"]2559.87[/TD]
[/TR]
</tbody>[/TABLE]

 

[makinmomb]

above data starts at a2 onwards , need formula on q2 to look car plate on above range and pull its value that is T456DJQ to pull 4144 and 472BLQ to pull 2095.125 , formula to drag down and work on the rest of the car plates

 

[steve the fish]

Provided not duplicates then this works:

=SUMPRODUCT(--($A$1:$M$4=A7),$C$1:$O$4)

Although you would be better to store the data properly.

A7 contains the plate

 

[makinmomb]

Thank you work as needed

 

[makinmomb]

I have these

[TABLE="width: 1222"]
<colgroup><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD]CONGO - KIN[/TD]
[TD="align: right"]148[/TD]
[TD][/TD]
[TD][/TD]
[TD]INARA - MUF [/TD]
[TD="align: right"]75[/TD]
[TD][/TD]
[TD][/TD]
[TD]INARA - LSK[/TD]
[TD="align: right"]65[/TD]
[TD][/TD]
[TD][/TD]
[TD]INARA - SOL [/TD]
[TD="align: right"]115[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]T519AYM[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]4144[/TD]
[TD]KIN[/TD]
[TD]T606BDL[/TD]
[TD="align: right"]31.549[/TD]
[TD="align: right"]2366.175[/TD]
[TD]MUFULILA[/TD]
[TD]BAE6521[/TD]
[TD="align: right"]27.301[/TD]
[TD="align: right"]1774.565[/TD]
[TD]LUSAKA[/TD]
[TD]T411CZS[/TD]
[TD="align: right"]30.797[/TD]
[TD="align: right"]3541.655[/TD]
[TD]SOLOWEZI[/TD]
[/TR]
[TR]
[TD]T633CGD[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]784[/TD]
[TD]KIN[/TD]
[TD]T690BKE[/TD]
[TD="align: right"]28.231[/TD]
[TD="align: right"]2117.325[/TD]
[TD]MUFULILA[/TD]
[TD]T877AXZ[/TD]
[TD="align: right"]25.558[/TD]
[TD="align: right"]1661.27[/TD]
[TD]LUSAKA[/TD]
[TD]T946AZV[/TD]
[TD="align: right"]31.058[/TD]
[TD="align: right"]956.493226[/TD]
[TD]SOLOWEZI[/TD]
[/TR]
[TR]
[TD]T686DEL[/TD]
[TD="align: right"]27.03[/TD]
[TD="align: right"]756.84[/TD]
[TD]KIN[/TD]
[TD]T108BVH[/TD]
[TD="align: right"]26.603[/TD]
[TD="align: right"]1995.225[/TD]
[TD]MUFULILA[/TD]
[TD]T144CKG[/TD]
[TD="align: right"]27.278[/TD]
[TD="align: right"]1773.07[/TD]
[TD]LUSAKA[/TD]
[TD]T229BKK[/TD]
[TD="align: right"]30.852[/TD]
[TD="align: right"]958.201416[/TD]
[TD]SOLOWEZI[/TD]
[/TR]
[TR]
[TD]T798CJH[/TD]
[TD="align: right"]28[/TD]
[TD="align: right"]756.84[/TD]
[TD]KIN[/TD]
[TD]T268BEN[/TD]
[TD="align: right"]28.765[/TD]
[TD="align: right"]2157.375[/TD]
[TD]MUFULILA[/TD]
[TD]BAE6510[/TD]
[TD="align: right"]27.419[/TD]
[TD="align: right"]1782.235[/TD]
[TD]LUSAKA[/TD]
[TD]T958CLY[/TD]
[TD="align: right"]28.504[/TD]
[TD="align: right"]879.405408[/TD]
[TD]SOLOWEZI[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]T414BFX[/TD]
[TD="align: right"]27.159[/TD]
[TD="align: right"]2036.925[/TD]
[TD]MUFULILA[/TD]
[TD]T717AXZ[/TD]
[TD="align: right"]27.67[/TD]
[TD="align: right"]1798.55[/TD]
[TD]LUSAKA[/TD]
[TD]T678BCD[/TD]
[TD="align: right"]28.417[/TD]
[TD="align: right"]809.998168[/TD]
[TD]SOLOWEZI[/TD]
[/TR]
</tbody>[/TABLE]

 

[makinmomb]

on v and w i have the following

[TABLE="width: 100"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]T124DCZ[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]T128DCZ[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]T137BHX[/TD]
[TD]820[/TD]
[/TR]
[TR]
[TD]T284DCM[/TD]
[TD]880[/TD]
[/TR]
[TR]
[TD]T352DMU[/TD]
[TD]815[/TD]
[/TR]
</tbody>[/TABLE]

 

[makinmomb]

The formula on w2 is as follows

=SUMPRODUCT(--($A$1:$R$5000=V2),$C$1:$T$5000)

It pulls the rate of the car at multiple columns

I need a similar formula where it will pull the car location that sits on d h L and p every 4th column of the plate number

 

[makinmomb]

If i dont have this formula then i have to search for 250 cars do it through CTRL F and plug manually the location which is too long

 

[makinmomb]

Will it sumproduct , i doubt as it is searching text and not summing

 

[makinmomb]

Anyone