# VLOOKUP

#### sm2200

##### Board Regular
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

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying
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?

Replies
0
Views
573
Replies
4
Views
266
Replies
9
Views
267
Replies
1
Views
541
Replies
3
Views
1K

1,203,462
Messages
6,055,565
Members
444,799
Latest member
CraigCrowhurst

### 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.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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