lookup a huge list

[znaya]

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:

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

 

[hatman]

EDIT: removed because solution does not return expected results.

 

[Aladin Akyurek]

=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]

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]

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]

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]

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]

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]

So I think I finally have it. With all of your caveats.

Book1
	A	B	C	D	E	F	G	H
1						Workday	Saturday	Sunday
2					a	1	2	7
3	9/4/2006	AmECmDAmBCBmDmE	19		b	2	3	8
4	9/5/2006	AmECmDAmBCBmDmE	19		c	3	4	9
5	9/6/2006	AmECmDAmBCBmDmE	19		d	4	5	10
6	9/7/2006	AmECmDAmBCBmDmE	19		e	5	6	11
7	9/8/2006	AmECmDAmBCBmDmE	19
8	9/9/2006	AmECmDAmBCBmDmE	24
9	9/10/2006	AmECmDAmBCBmDmE	49
10	9/11/2006	AmECmDAmBCBmDmE	19
11	9/12/2006	AmECmDAmBCBmDmE	19
12	9/13/2006	AmECmDAmBCBmDmE	19
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.