# an interesting lookup problem

#### simonf

##### Board Regular
Book1
ABCDEFGH
1Table1
2minniegoofymickeydonaldpluto
3section1section2section3
4AHN1234
5BIO4510
6CJP1624869
7EKQ1711711
8FLR20148
9GMS561212
10
11Table2
12minniegoofymickeydonaldpluto
13
14
1525062347156815863568
16
17Solution
18sectiongoofymickeydonaldpluto
19A2506
20J12544
21K
22Q
23N
24G
25S
Sheet1

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

##### MrExcel MVP
what's the problem!

#### simonf

##### Board Regular
Here's my problem. I need to extract the information from table 1 and 2 to the solution table. Table 2 is pretty easy using Hlookup but table 1 is kinna tough since the section is across 3 columns. And I can't put them all in a single row. Would Sumproduct work? Can I do a combination of matching the section value from Solution Table to the section value in Table 2? But how do I extract the cross section information from the row of Disneys? Would the formula be something like:

SUMPRODUCT(MATCH)*HLOOKUP

Help will be much appreciated. Thanks.

#### simonf

Any takers?

##### MrExcel MVP

Are you willing to make a few additions to your data? Will the values in cells A4:C9 ever change their positions? Are we limited to native excel functions, or can we use addin functions?

#### Alriemer

##### Board Regular
SimonF,

Here's part of an approach. I don't understand how the combination of a name and a letter picks out the two numbers you want to multiply. But it looks as though the letter determines the row of the data point in Table 1. Do the letters have to be text? If numbers, you could use something like int((value+(n-1))/n) to reduce them to row numbers, where n is the number of rows in the table. Try it on the series 1,2,3... with n=3.
Alex

##### MrExcel MVP

The main problem is with the letter lookup - you have to lookup in the entire array A4:C9. although there are ways to do this, I thought the easiest approach would be to concatenate the letters in each row into a single value, then do & index / match...(see d4:d9 in the example).

MATCH("*"&\$A18&"*",\$D\$4:\$D\$9,0)
-to find the position of the letter in the new strings

MATCH(\$B\$17,\$E\$2:\$I\$2,0)
- to match the charachter name to the right column in the data table

=(INDEX(\$E\$4:\$I\$9,MATCH("*"&\$A18&"*",\$D\$4:\$D\$9,0),MATCH(\$B\$17,\$E\$2:\$I\$2,0)))
- put them together to return the correct value from the data table

INDEX(\$A\$15:\$E\$15,1,MATCH(\$B\$17,\$A\$12:\$E\$12,0))
- return the value from the second table.

See the example:

EDIT: removed 'cos too big...

This message was edited by PaddyD on 2002-09-24 14:58

#### simonf

##### Board Regular
Wow, cool! Let me try this out and let you guys know what happened. Also after I look more into this problem, I found out I have sooo many more conditions to tag on that a general formula will consist of more If's than the cow come home. Since there was a time crunch, I just did a quick fix using hardcode cell references. I'll work more on this problem and keep you updated. Thanks for all your helps. What a wonderful board!

#### simonf

##### Board Regular
This works GREAT! I never thought of approaching the solution in this manner. I have learned not only technical skills on this board but more importantly, the way to approach problems. Thanks for all the help, especially PaddyD.

Replies
2
Views
83
Replies
1
Views
104
Replies
1
Views
92
Replies
3
Views
147
Replies
40
Views
597

1,148,011
Messages
5,744,334
Members
423,863
Latest member
teehexcel

### 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.

### Which adblocker are you using?

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

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