lookup a huge list

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55
i have a huge list and i want to look it up in a huge table.

the problem is that some cells have more than a character. adicionally some characters should be ignore and not looked up in that table.
i want to to create a formula so that the result, in that cell, is the sum of the lookups.


example:


Code:
A	VLOOKUP("A";D1:E5;2)
B	VLOOKUP("B";D1:E5;2)
AB	VLOOKUP("A";D1:E5;2) + VLOOKUP("B";D1:E5;2)
ABC	VLOOKUP("A";D1:E5;2) + VLOOKUP("B";D1:E5;2) + VLOOKUP("C";D1:E5;2)
AmB	VLOOKUP("B";D1:E5;2) [Am is ignored]
ABmC	VLOOKUP("A";D1:E5;2) + VLOOKUP("B";D1:E5;2) [Bm is ignored]

thanks in advance
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
EDIT: removed because solution does not return expected results.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
=SUM(IF(ISNUMBER(SEARCH("A",D1:D5))+ISNUMBER(SEARCH("B",D1:D5)),E1:E5))

which must be confirmed with control+shift+enter, not just with enter.
 

NBVC

Well-known Member
Joined
Aug 31, 2005
Messages
5,828
Try:

=SUMPRODUCT(ISNUMBER(SEARCH("A",$D$1:$D$5))+ISNUMBER(SEARCH("B",$D$1:$D$5))+ISNUMBER(SEARCH("C",$D$1:$D$5)),$E$1:$E$5)

or

=SUM(IF((ISNUMBER(SEARCH({"A","B","C"},$D$1:$D$5))),$E$1:$E$5)) confirmed with CTRL+SHIFT+ENTER not just ENTER
 

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55

ADVERTISEMENT

i'm sorry... only now i realized i missed important information.
as far as i understand your formulas treat every letter as having always the same value. it doesn't happen.
their as also dependent on date. there is a column with dates and, i, for example, if date on the given row is a workday, vlookup will retrieve the second value on table, if it is saturday it will retrieve the third value, if it is sunday it will retrieve the fourth.... so, A doesn't have always the same value, neither has B, C, D...

this problem is driving me mad.

i'd like to avoid macros as i wanted to distribute the spreadsheet to excel and openoffice calc users (macros are not compatible...)

What is the criteria for what gets skipped? The letter "m" plus whatever precedes it?
yes, it could be the criteria.
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
What is the criteria for what gets skipped? The letter "m" plus whatever precedes it?
yes, it could be the criteria.

That is not very helpful... if something gets skipped, the criteria is necessary so an accurate formula can be built. Otherwise, you'll get erroneous results.
 

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55

ADVERTISEMENT

That is not very helpful... if something gets skipped, the criteria is necessary so an accurate formula can be built. Otherwise, you'll get erroneous results.

ok, let's assume ?m should be ignored
 

znaya

Board Regular
Joined
Apr 22, 2006
Messages
55
tonight i've thought over the situation, i've done a lot of research and still didn't find an easy solution.

if user inputs "ABmC"... do you think i could use FIND, LEN, RIGHT, LEFT, MIDDLE, TRIM, SUBSTITUTE, a lot of IFs and VLOOKUPs and, in the final, be able to have a column for ABC and other for AmBmCm?

in the same example:

A1="ABmC"

for ABC shifts
SUBSTITUTE (A1;" ";"");
LEN(A1);
count the number of "m" in the cell;
verify the positions of "m" in the cell;
delete "m"position and "m"-1 position.
verify how many characters are left...
SUM(vlookup each of the left characters)

for AmBmCm shifts
SUBSTITUTE (A1;" ";"");
LEN(A1);
count the number of "m" in the cell;
verify the positions of "m" in the cell;
SUM(vlookup "m"-1&"m".)

this is still a huge mess... isn't it? :(
 

hatman

Well-known Member
Joined
Apr 8, 2005
Messages
2,664
So I think I finally have it. With all of your caveats.
Book1
ABCDEFGH
1WorkdaySaturdaySunday
2a127
39/4/2006AmECmDAmBCBmDmE19b238
49/5/2006AmECmDAmBCBmDmE19c349
59/6/2006AmECmDAmBCBmDmE19d4510
69/7/2006AmECmDAmBCBmDmE19e5611
79/8/2006AmECmDAmBCBmDmE19
89/9/2006AmECmDAmBCBmDmE24
99/10/2006AmECmDAmBCBmDmE49
109/11/2006AmECmDAmBCBmDmE19
119/12/2006AmECmDAmBCBmDmE19
129/13/2006AmECmDAmBCBmDmE19
Sheet1


C3: =SUM(IF(MID(B3,ROW(INDIRECT("1:"&LEN(B3)))+1,1)="m",0,IF(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1)="m",0,LOOKUP(MID(B3,ROW(INDIRECT("1:"&LEN(B3))),1),$E$2:$E$6,INDIRECT(IF(WEEKDAY(A3,2)<6,"Workday",IF(WEEKDAY(A3,2)=6,"Saturday","Sunday"))))))) confirmed with Ctrl+Shift+Enter

Here are the rules:
1) F2:F6 is a Named Range called Workday
2) G2:G6 is a Named Rangecalled Saturday
3) H2:H6 is a Named Range called Sunday
4) The letters in column E must be in ascending alphabetical order. The table can be expanded as needed, just shift the references.
5) This formula is NOT case sensitive... hope that's okay.

Let me know how you make out.
 

Forum statistics

Threads
1,136,352
Messages
5,675,274
Members
419,559
Latest member
BraytonM

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
Top