Difficulty trying to write vlookup to concatenated cell

[Rick G]

On sheet 1, I have concatenated the values of cells A12, C12 & E12 into F12. The concatenate formula remains intact.

On sheet 2, I have concatenated the values of cells A2, B2 & D2 into E2. I then copied the result in cell E2 down to row 145 into cell E145. Finally I performed the copy/paste special - values exercise from row E2 to E145 on sheet 2.

There is information to the right of column E on sheet 2 that I would like to move into sheet 1 (to the right of column F).

From sheet 1, I am writing a vlookup formula in cell G12 that looks at the concatenated value in cell E12 and searches for the result of the match in sheet 2 within the range $E$2:$F$145. The column containing the result I am looking for resides in column F of sheet 2.

I keep getting a #N/A error formula even though I know for a fact that the match of cell F12 on sheet 1 is found within the range of $E$2:$F$145 on sheet 2.

I had read previously that vlookups and concatenated cells don't really like each other.

I have no experience with Match and Index.

Does anyone know a way to solve my problem?

Thank you,

Rick

 

[Robert Mika]

Look at this post:

http://www.mrexcel.com/forum/showthread.php?p=2828956#post2828956

An example of INDEX/MATCH with use of ampersand.

 

[Rick G]

In spite of painstakingly following all of the steps you outlined, I still am coming up with a #N/A as a result.

Since I cannot copy/paste my spreadsheet in here, I'll tell you what's in the columns and perhaps you can tell me where the problem lies:

On Sheet 1

Cell A12 = A
Cell B12 = Great Lakes
Cell C12 = 36" x 80"
Cell D12 = This is the cell that needs to return the value of the Index & Match Formulas
Cells E12:E155 = Contains corresponding values found in column A
Cells F12:F155 = Contains corresponding values found in column B
Cells H12:H155 = Contains correspondinig values found in column C
Cells I12:I155 = Contains corresponding Item numbers I want to return to cell D12

In cell D12, I wrote the following Formula:

{=INDEX($E$12:$I$155,MATCH(A12&B12&C12,$E$12:$E$155&$F$12:$F$155&$H$12:$H$155,0),5)}

What's wrong with this formula?

Thanks!

 

[CARBOB]

Rick,
Goto the thread below and download Mr Schollar's add-in. Then you will be able to post your work sheet with examples. Very easy to use.


http://www.mrexcel.com/forum/showthread.php?t=399380&highlight=SCHOLLAR&page=2


example

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L
67	1	2	4	17	1	11	28	13	2	11	54
294	1	3	6	20	1	11	25	40	9	11	85
372	1	2	6	40	1	11	28	40	8	11	87
373	1	4	6	40	1	11	13	40	8	11	72
666	1	6	8	23	1	11	40	1	4	11	56
683	1	5	8	26	1	11	5	1	5	11	22
810	1	3	9	12	1	11	25	6	0	11	42
MM DB

Excel 2007

Cell Formulas
Range	Formula
G67	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B67,DRAWS!$AC$14:$AC$57,0))
G294	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B294,DRAWS!$AC$14:$AC$57,0))
G372	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B372,DRAWS!$AC$14:$AC$57,0))
G373	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B373,DRAWS!$AC$14:$AC$57,0))
G666	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B666,DRAWS!$AC$14:$AC$57,0))
G683	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B683,DRAWS!$AC$14:$AC$57,0))
G810	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(B810,DRAWS!$AC$14:$AC$57,0))
H67	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C67,DRAWS!$AC$14:$AC$57,0))
H294	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C294,DRAWS!$AC$14:$AC$57,0))
H372	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C372,DRAWS!$AC$14:$AC$57,0))
H373	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C373,DRAWS!$AC$14:$AC$57,0))
H666	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C666,DRAWS!$AC$14:$AC$57,0))
H683	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C683,DRAWS!$AC$14:$AC$57,0))
H810	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(C810,DRAWS!$AC$14:$AC$57,0))
I67	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D67,DRAWS!$AC$14:$AC$57,0))
I294	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D294,DRAWS!$AC$14:$AC$57,0))
I372	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D372,DRAWS!$AC$14:$AC$57,0))
I373	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D373,DRAWS!$AC$14:$AC$57,0))
I666	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D666,DRAWS!$AC$14:$AC$57,0))
I683	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D683,DRAWS!$AC$14:$AC$57,0))
I810	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(D810,DRAWS!$AC$14:$AC$57,0))
J67	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E67,DRAWS!$AC$14:$AC$57,0))
J294	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E294,DRAWS!$AC$14:$AC$57,0))
J372	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E372,DRAWS!$AC$14:$AC$57,0))
J373	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E373,DRAWS!$AC$14:$AC$57,0))
J666	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E666,DRAWS!$AC$14:$AC$57,0))
J683	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E683,DRAWS!$AC$14:$AC$57,0))
J810	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(E810,DRAWS!$AC$14:$AC$57,0))
K67	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F67,DRAWS!$AC$14:$AC$57,0))
K294	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F294,DRAWS!$AC$14:$AC$57,0))
K372	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F372,DRAWS!$AC$14:$AC$57,0))
K373	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F373,DRAWS!$AC$14:$AC$57,0))
K666	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F666,DRAWS!$AC$14:$AC$57,0))
K683	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F683,DRAWS!$AC$14:$AC$57,0))
K810	=INDEX(DRAWS!$AE$14:$AE$57,MATCH(F810,DRAWS!$AC$14:$AC$57,0))
L67	=SUM(G67:J67)
L294	=SUM(G294:J294)
L372	=SUM(G372:J372)
L373	=SUM(G373:J373)
L666	=SUM(G666:J666)
L683	=SUM(G683:J683)
L810	=SUM(G810:J810)

 

[Rick G]

I've downloaded the add-in and see it as a new tool bar.

I have the file opened and have selected 97 rows (since the HTML maker won't allow me to select more than 100 rows).

The Word Document explaining the process isn't really clear on what to do next.

How do I paste my document into this message board as HTML? When I used the tool bar and wanted to generate HTML, I'm not sure what to do next. I tried pasting it in here and got a bunch of code (lots & lots of code) but no spreadsheet.

I must seem brain dead but usually I'm pretty good at following clear instructions.

Now what?

 

[Rick G]

Oh well, it looks sloppy but it does copy/paste well into Excel.

Excel 2003
A B C D E F G H I
11 Option (Please Choose from Drop-Down) E & I Region (Please Choose from Drop-Down) Mattress Dimensions (Please Use Drop-Down) Part # OPTION E&I Region Made Rite Product # Dimensions Part #
12 A Great Lakes 36" X 80" #N/A A Great Lakes FE-1800OXV7 36" X 80” 5544FE1800OXV780GL
13 A Great Lakes FE-1800OXV7 38" X 80” 5544FE1800OXV733XLGL
14 B Great Lakes FE-1800OX7 36" X 80” 5544FE1800OX780GL
15 B Great Lakes FE-1800OX7 38" X 80” 5544FE1800OX733XLGL
16 C Great Lakes FE-1500OX6 36" X 80” 5544FE1500OX680GL
17 E Great Lakes SPC-1400OX6 36" X 80” 5544SPC1400OX680GL
18 A Central FE-1800OXV7 36" X 80” 5544FE1800OXV780CT
19 A Central FE-1800OXV7 38" X 80” 5544FE1800OXV733XLCT
20 B Central FE-1800OX7 36" X 80” 5544FE1800OX780CT
21 B Central FE-1800OX7 38" X 80” 5544FE1800OX733XLCT
22 C Central FE-1500OX6 36" X 80” 5544FE1500OX680CT
23 E Central SPC-1400OX6 36" X 80” 5544SPC1400OX680CT
24 A Mid Atlantic FE-1800OXV7 36" X 80” 5544FE1800OXV780MA
25 A Mid Atlantic FE-1800OXV7 38" X 80” 5544FE1800OXV733XLMA
26 B Mid Atlantic FE-1800OX7 36" X 80” 5544FE1800OX780MA
27 B Mid Atlantic FE-1800OX7 38" X 80” 5544FE1800OX733XLMA
28 C Mid Atlantic FE-1500OX6 36" X 80” 5544FE1500OX680MA
29 E Mid Atlantic SPC-1400OX6 36" X 80” 5544SPC1400OX680MA
30 A Northeast FE-1800OXV7 36" X 80” 5544FE1800OXV780NE
31 A Northeast FE-1800OXV7 38" X 80” 5544FE1800OXV733XLNE
32 B Northeast FE-1800OX7 36" X 80” 5544FE1800OX780NE
33 B Northeast FE-1800OX7 38" X 80” 5544FE1800OX733XLNE
34 C Northeast FE-1500OX6 36" X 80” 5544FE1500OX680NE
35 E Northeast SPC-1400OX6 36" X 80” 5544SPC1400OX680NE
36 A Southeast FE-1800OXV7 36" X 80” 5544FE1800OXV780SE
37 A Southeast FE-1800OXV7 38" X 80” 5544FE1800OXV733XLSE
38 B Southeast FE-1800OX7 36" X 80” 5544FE1800OX780SE
39 B Southeast FE-1800OX7 38" X 80” 5544FE1800OX733XLSE
40 C Southeast FE-1500OX6 36" X 80” 5544FE1500OX680SE
41 E Southeast SPC-1400OX6 36" X 80” 5544SPC1400OX680SE
42 A Western FE-1800OXV7 36" X 80” 5544FE1800OXV780WT
43 A Western FE-1800OXV7 38" X 80” 5544FE1800OXV733XLWT
44 B Western FE-1800OX7 36" X 80” 5544FE1800OX780WT
45 B Western FE-1800OX7 38" X 80” 5544FE1800OX733XLWT
46 C Western FE-1500OX6 36" X 80” 5544FE1500OX680WT
47 E Western SPC-1400OX6 36" X 80” 5544SPC1400OX680WT
48 A Great Lakes FE-1800OXV7 36" X 80” 5544FE1800OXV780GL
49 A Great Lakes FE-1800OXV7 38" X 80” 5544FE1800OXV733XLGL
50 B Great Lakes FE-1800OX7 36" X 80” 5544FE1800OX780GL
51 B Great Lakes FE-1800OX7 38" X 80” 5544FE1800OX733XLGL
52 C Great Lakes FE-1500OX6 36" X 80” 5544FE1500OX680GL
53 E Great Lakes SPC-1400OX6 36" X 80” 5544SPC1400OX680GL
54 A Central FE-1800OXV7 36" X 80” 5544FE1800OXV780CT
55 A Central FE-1800OXV7 38" X 80” 5544FE1800OXV733XLCT
56 B Central FE-1800OX7 36" X 80” 5544FE1800OX780CT
57 B Central FE-1800OX7 38" X 80” 5544FE1800OX733XLCT
58 C Central FE-1500OX6 36" X 80” 5544FE1500OX680CT
59 E Central SPC-1400OX6 36" X 80” 5544SPC1400OX680CT
60 A Mid Atlantic FE-1800OXV7 36" X 80” 5544FE1800OXV780MA
61 A Mid Atlantic FE-1800OXV7 38" X 80” 5544FE1800OXV733XLMA
62 B Mid Atlantic FE-1800OX7 36" X 80” 5544FE1800OX780MA
63 B Mid Atlantic FE-1800OX7 38" X 80” 5544FE1800OX733XLMA
64 C Mid Atlantic FE-1500OX6 36" X 80” 5544FE1500OX680MA
65 E Mid Atlantic SPC-1400OX6 36" X 80” 5544SPC1400OX680MA
66 A Northeast FE-1800OXV7 36" X 80” 5544FE1800OXV780NE
67 A Northeast FE-1800OXV7 38" X 80” 5544FE1800OXV733XLNE
68 B Northeast FE-1800OX7 36" X 80” 5544FE1800OX780NE
69 B Northeast FE-1800OX7 38" X 80” 5544FE1800OX733XLNE
70 C Northeast FE-1500OX6 36" X 80” 5544FE1500OX680NE
71 E Northeast SPC-1400OX6 36" X 80” 5544SPC1400OX680NE
72 A Southeast FE-1800OXV7 36" X 80” 5544FE1800OXV780SE
73 A Southeast FE-1800OXV7 38" X 80” 5544FE1800OXV733XLSE
74 B Southeast FE-1800OX7 36" X 80” 5544FE1800OX780SE
75 B Southeast FE-1800OX7 38" X 80” 5544FE1800OX733XLSE
76 C Southeast FE-1500OX6 36" X 80” 5544FE1500OX680SE
77 E Southeast SPC-1400OX6 36" X 80” 5544SPC1400OX680SE
78 A Western FE-1800OXV7 36" X 80” 5544FE1800OXV780WT
79 A Western FE-1800OXV7 38" X 80” 5544FE1800OXV733XLWT
80 B Western FE-1800OX7 36" X 80” 5544FE1800OX780WT
81 B Western FE-1800OX7 38" X 80” 5544FE1800OX733XLWT
82 C Western FE-1500OX6 36" X 80” 5544FE1500OX680WT
83 E Western SPC-1400OX6 36" X 80” 5544SPC1400OX680WT
84 A Great Lakes FE-1800OXV7 36" X 80” 5544FE1800OXV780GL
85 A Great Lakes FE-1800OXV7 38" X 80” 5544FE1800OXV733XLGL
86 B Great Lakes FE-1800OX7 36" X 80” 5544FE1800OX780GL
87 B Great Lakes FE-1800OX7 38" X 80” 5544FE1800OX733XLGL
88 C Great Lakes FE-1500OX6 36" X 80” 5544FE1500OX680GL
89 E Great Lakes SPC-1400OX6 36" X 80” 5544SPC1400OX680GL
90 A Central FE-1800OXV7 36" X 80” 5544FE1800OXV780CT
91 A Central FE-1800OXV7 38" X 80” 5544FE1800OXV733XLCT
92 B Central FE-1800OX7 36" X 80” 5544FE1800OX780CT
93 B Central FE-1800OX7 38" X 80” 5544FE1800OX733XLCT
94 C Central FE-1500OX6 36" X 80” 5544FE1500OX680CT
95 E Central SPC-1400OX6 36" X 80” 5544SPC1400OX680CT
96 A Mid Atlantic FE-1800OXV7 36" X 80” 5544FE1800OXV780MA
97 A Mid Atlantic FE-1800OXV7 38" X 80” 5544FE1800OXV733XLMA
98 B Mid Atlantic FE-1800OX7 36" X 80” 5544FE1800OX780MA
99 B Mid Atlantic FE-1800OX7 38" X 80” 5544FE1800OX733XLMA
100 C Mid Atlantic FE-1500OX6 36" X 80” 5544FE1500OX680MA
101 E Mid Atlantic SPC-1400OX6 36" X 80” 5544SPC1400OX680MA
102 A Northeast FE-1800OXV7 36" X 80” 5544FE1800OXV780NE
103 A Northeast FE-1800OXV7 38" X 80” 5544FE1800OXV733XLNE
104 B Northeast FE-1800OX7 36" X 80” 5544FE1800OX780NE
105 B Northeast FE-1800OX7 38" X 80” 5544FE1800OX733XLNE
106 C Northeast FE-1500OX6 36" X 80” 5544FE1500OX680NE
107 E Northeast SPC-1400OX6 36" X 80” 5544SPC1400OX680NE
Pricing Calculator


Array Formulas
Cell Formula
D12 {=INDEX($E$12:$I$155,MATCH(A12&B12&C12,$E$12:$E$155&$F$12:$F$155&$H$12:$H$155,0),5)}
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

 

[CARBOB]

Rick,

Select the data you want to show, then click the add-in button. Should show MREXCEL HTML. Then select default, goto thread and paste, after pasting, lots of jumble up code, then post.

 

[gregtx81]

One issue is the second quote in

36" X 80" vs. 36" X 80”

is not the same character so match won't work.

One of them looks italicized...use the code() function to verify.

 

[Rick G]

Whoohoo!

I simply removed the quotation marks ("), which is the abbreviation for the unit of measurement of inch, and the formula worked perfectly!!!!

Apparently, it looks like Index and Match don't like those types of characters. I'll have to remember that for the future!

Thank you so much to everyone who helped.

I now understand the formula and how it works.

I spent 5 hours (that I didn't really have to spend yesterday) trying to figure this out and went home very frustrated.

You people just made my day. This is a game changer!

Thank you again everyone!