Vlookup when data contains commas

H

helpme20

Board Regular
Joined
Aug 28, 2010
Messages
102
I have a spreadsheet that has the following:

in Sheet 1:

Column A has Product codes.

Example:

A1 = 132456
A2 = 187267, 839465, 908734
A3 = 145034, 387460, 284784


Column B is the Price increase amounts for the corresponding product codes.

Example:

B1 = .067
B2 = .066
B3 = .102

In Sheet 2:



How do I write a vlookup for A1:B3 that will return .066 when I am trying to find how much the price was increased when I enter Product code 839465?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Sort by date Sort by votes
Excel Workbook
DE
151324560.067
16187267, 839465, 9087340.066
17145034, 387460, 2847840.102
18
19Lookup valueReturns
202847840.102
Sheet1




this will do it, there's 2 tests. 1 for a number and one for a text representation of a number.
 
Upvote 0
For errors, if you have Excel 2007 or later use

Excel Workbook
DE
151324560.067
16187267, 839465, 9087340.066
17145034, 387460, 2847840.102
18
19Kookup valueReturns
208394650.066
Sheet1


and for earlier versions

Excel Workbook
DE
151324560.067
16187267, 839465, 9087340.066
17145034, 387460, 2847840.102
18
19Kookup valueReturns
209087340.066
Sheet1
 
Upvote 0
helpme20 said:
I have a spreadsheet that has the following:

in Sheet 1:

Column A has Product codes.

Example:

A1 = 132456
A2 = 187267, 839465, 908734
A3 = 145034, 387460, 284784


Column B is the Price increase amounts for the corresponding product codes.

Example:

B1 = .067
B2 = .066
B3 = .102

In Sheet 2:



How do I write a vlookup for A1:B3 that will return .066 when I am trying to find how much the price was increased when I enter Product code 839465?
Click to expand...
Here's another one...

Book1
AB
11324560.067
2187267, 839465, 9087340.066
3145034, 387460, 2847840.102
4
5
6Look up value:Result:
71324560.067
Sheet1


Book1
A
10Formula entered in B7:
11=SUMPRODUCT(--(ISNUMBER(SEARCH(A7,A1:A3))),B1:B3)
Sheet1
 
Upvote 0
T. Valko said:
Here's another one...

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 174px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-RIGHT: #000000 1px solid">132456</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-LEFT-COLOR: #000000; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0.067</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">187267, 839465, 908734</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0.066</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">145034, 387460, 284784</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0.102</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid"></TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">Look up value:</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">Result:</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: left; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">132456</TD><TD style="BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: right; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-LEFT-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">0.067</TD></TR></TBODY></TABLE>



Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Verdana,Arial; FONT-SIZE: 10pt" border=0 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 400px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP: #000000 1px solid; FONT-WEIGHT: bold; BORDER-RIGHT: #000000 1px solid">Formula entered in B7:</TD></TR><TR style="HEIGHT: 24px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid">=SUMPRODUCT(--(ISNUMBER(SEARCH(A7,A1:A3))),B1:B3)</TD></TR></TBODY></TABLE>
Click to expand...


This works very well. Using your example above, If I were to add a column in between A and B that would have the Package Type (it would either say "BLK" or "PKG", How I still lookup the value in Cell A7 (132456) and return "PKG" into Cell B7 since it isn't a number?
 
Upvote 0
helpme20 said:
This works very well. Using your example above, If I were to add a column in between A and B that would have the Package Type (it would either say "BLK" or "PKG", How I still lookup the value in Cell A7 (132456) and return "PKG" into Cell B7 since it isn't a number?
Click to expand...
Try this...

Book1
AB
1132456BLK
2187267, 839465, 908734PKG
3145034, 387460, 284784BLK
4
5
6Lookup value:Result:
7387460BLK
Sheet2

Array entered**:

=INDEX(B1:B3,MATCH(TRUE,ISNUMBER(SEARCH(A7,A1:A3)),0))

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
 
Upvote 0
In B1 on Sheet2 Just enter...

=LOOKUP(9.99999999999999E+307,SEARCH(A1,Sheet1!$A$1:$A$3),Sheet1!$B$1:$B$3)

where A1 houses a look up value of interest. This formula would fetch any type of value from B1:B3...
 
Upvote 0
Aladin Akyurek said:
=LOOKUP(9.99999999999999E+307,SEARCH(A1,Sheet1!$A$1:$A$3),Sheet1!$B$1:$B$3)
Click to expand...
This does the same thing, is easier to read and saves a few keystrokes...

=LOOKUP(1E100,SEARCH(A1,Sheet1!$A$1:$A$3),Sheet1!$B$1:$B$3)
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Excel VBA - Sort & Get Totals - Logic
Replies
4
Views
418
STARL0RD
S
A
  • Solved
Target.address and VLOOKUP with a range of cells
Replies
8
Views
381
RoryA
RoryA
D
  • Solved
VLOOKUP not working when there is more than one entry of the same value
Replies
5
Views
223
Joe4
Joe4
L
  • Question Question
MACROS WORK WELL, BUT WHEN I DELETE THE DATA TO LEAVE THE FILE WITHOUT INFORMATION, MACROS DON'T WORK
Replies
1
Views
212
leobrice
L
K
  • Question Question
Return a user specified number of cell values to a comma list randomly
Replies
1
Views
327
Eric W
Eric W

Forum statistics

Threads
1,224,550
Messages
6,179,459
Members
452,915
Latest member
hannnahheileen

Share this page

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?

Disable AdBlock

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
Back
Top