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

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
=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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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? :(
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,216,108
Messages
6,128,872
Members
449,475
Latest member
Parik11

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