# lookup a huge list

#### znaya

##### Board Regular
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]``````

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
EDIT: removed because solution does not return expected results.

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

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

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.

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.

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

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?

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.

Replies
6
Views
238
Replies
0
Views
244
Replies
1
Views
150
Replies
7
Views
144
Replies
1
Views
302

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.

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