an interesting lookup problem

simonf

Board Regular
Joined
Jun 18, 2002
Messages
207
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
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?

Paddy
 
Upvote 0
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
 
Upvote 0
OK - how about this...

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


Paddy
This message was edited by PaddyD on 2002-09-24 14:58
 
Upvote 0
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! :cool:
 
Upvote 0
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. :cool:
 
Upvote 0

Forum statistics

Threads
1,213,556
Messages
6,114,284
Members
448,562
Latest member
Flashbond

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