VLOOKUP

[sm2200]

On Oct. 4, 2004, I posted the following question with a correction. If I have the following data in the following cells (Colms A,B,C ROWS 1,2,3 ):

(A1) ABC,XY (B1) 5 (C1) 10
(A2) SAVU,TST (B2) 12 (C2) 8
(A3) ACD,XX (B3) 7 (C3) 6
(A4) MO,RJR (B4) 11 (C4) 15
(A5) ACDU,VV (B5) 45 (C5) 17
(A6) AC,TS (B6) 19 (C6) 22
(A7) ADC,RJ (B7) 61 (C7) 24

Based on the first part of the data in Colm A, what would be the VLOOKUP formula to find the corresponding number in Colm B, if only the text up to & including the comma is given.
For example for ACD , the corresponding looked up number would be 7 and for AC, the corresponding looked up number would be 19.

The solution you (Domenic) gave was :
=VLOOKUP(A10&",*", A1:C7 , 2, 0) which worked fine.

But since then I've found some symbols or text in the first colm (Colm A ) that do not have commas but stand alone. For example I'll rewrite the previous example to show what I mean. The changes are in Colm A of Rows 3 & 4. Everything else is the same.


(A1) ABC,XY (B1) 5 (C1) 10
(A2) SVU,TST (B2) 12 (C2) 8
(A3) ACD (B3) 7 (C3) 6
(A4) MO (B4) 11 (C4) 15
(A5) ACDU,VV (B5) 45 (C5) 17
(A6) AC,TS (B6) 19 (C6) 22
(A7) ADC,RJ (B7) 61 (C7) 24

Based on the data in Colm A:
If the symbol is SVU the looked up no. in colm B would be 12. If the symbol is ACD the no. would be 7. If the symbol is MO the no. would be 11.

What would be the VLOOKUP formula to find the corresponding no. in Colm B, if only the symbol is given and there may or may not be a comma and text after the symbol in Colm A ?

Thanks again,

Sam Marx

sgm2200@adelphia.net

 

[Norie]

Sam

Just remove the comma from the VLOOKUP formula Domenic posted:

=VLOOKUP(A10&"*",A1:C7,2,0)

BTW have you considered seperating the data in column A using Data>Text to Columns... using comma as a delimiter?