Multiple column VLOOKUP

[makinmomb]

I have this on a to o columns

T456DJQ	28	4144	T637DFR	28.664	2149.8	T293DCM	27.302	1775	T674BUX	28.509	3278.535	T452DJR	27.074	2572.03
T785DDS	28	4144	T472BLQ	27.935	2095.125	T406CZS	27.346	747	T137BHX	28.453	3272.095	T706CWY	26.955	2560.725
T468DDA	28	4144	T532AWW	27.434	2057.55	BAE6520	27.283	746	T690BKE	28.456	3272.44	T229BKK	27.121	2576.495
T960BCA	28	4144	T245DEZ	27.491	2061.825	T442BJS	26.652	727	T316CBX	28.516	3279.34	T699BFJ	26.946	2559.87

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[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

CONGO - KIN	148			INARA - MUF	75			INARA - LSK	65			INARA - SOL	115
T519AYM	28	4144	KIN	T606BDL	31.549	2366.175	MUFULILA	BAE6521	27.301	1774.565	LUSAKA	T411CZS	30.797	3541.655	SOLOWEZI
T633CGD	28	784	KIN	T690BKE	28.231	2117.325	MUFULILA	T877AXZ	25.558	1661.27	LUSAKA	T946AZV	31.058	956.493226	SOLOWEZI
T686DEL	27.03	756.84	KIN	T108BVH	26.603	1995.225	MUFULILA	T144CKG	27.278	1773.07	LUSAKA	T229BKK	30.852	958.201416	SOLOWEZI
T798CJH	28	756.84	KIN	T268BEN	28.765	2157.375	MUFULILA	BAE6510	27.419	1782.235	LUSAKA	T958CLY	28.504	879.405408	SOLOWEZI
				T414BFX	27.159	2036.925	MUFULILA	T717AXZ	27.67	1798.55	LUSAKA	T678BCD	28.417	809.998168	SOLOWEZI

<colgroup><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[makinmomb]

on v and w i have the following

T124DCZ	0
T128DCZ	0
T137BHX	820
T284DCM	880
T352DMU	815

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[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