# VLOOKUP doesn't work in an array formula, apparently

#### larryk

##### New Member
I have a list of eight text items and their respective numerical values in A2:B9. There are duplicates in the item names and I am looking for value totals for some items (which I have calculated using SUMIF).

Now I would like a total of all things that I haven't got totals for (i.e. "all the rest"). I can do this in two steps by doing a bunch of =ISNA(VLOOKUP(A2,\$A\$11:\$A\$12,1,FALSE)) in column D (where A11 and A12 contain the text items that i have already counted), and then a =SUMIF(D2:D9, TRUE, B2:B9) to find the total but I'd like to do it in one step.

I read about array formulas and constructed the following but it doesn't seem to work: {=SUM(IF(ISNA(VLOOKUP(A2:A9, A11:A12, 1, FALSE)), B2:B9))}. Is my syntax wrong? Or is it because VLOOKUP uses an array as an argument anyway and that's getting messed-up by the array formula processing, or something?

I can post the example worksheet if you like (and if someone can point me to the nice web-worksheet html).

Any help much appreciated,
Larry

P.S. I did it in an array formula with a nasty CONCATENATE hack but when I transfer this to my real spreadsheet if there are any substrings or overlap then the numbers may be wrong. I'd like to do it "properly"

{=SUM(IF(ISERROR(FIND(A2:A9,CONCATENATE(A11,A12))),B2:B9))}

### Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

#### larryk

##### New Member
I found HtmlMaker
arrayformula.xls
ABCD
1data2-stepsolution
2a1FALSE
3b1FALSE
4c5TRUE
5d5TRUE
6a1FALSE
7b1FALSE
8c5TRUE
9d5TRUE
10totals20
11a2<--here'sthetotalofa
12b2<--here'sthetotalofb
13others0<--thisshouldbethetotalofothersbutdoesn'twork!
14concatenatehack20
Sheet1

#### Brian from Maui

##### MrExcel MVP
larryk said:
Now I would like a total of all things that I haven't got totals for (i.e. "all the rest").

How many criterias? Couldn't you use a not equal too?

=SUMIF(A1:A10," <> Criteria",B1:B10)

Book1.xls
ABCD
1
2a1
3b1
4c5
5d5
6a1
7b1
8c5
9d5
10
11a2
12b2
13others20
Sheet5

#### larryk

##### New Member
Thanks, yes that is a solution to the example problem...

...but not an answer to my question. As soon as I transfer this back into my spreadsheet it becomes a lot more complicated.

I need to work out how to sumif where the criteria is that the text value being compared is not in a certain list.

Can you tell me why the vlookup doesn't work in {}?

Cheers,
Larry

#### just_jon

##### Legend
Use

=SUMPRODUCT(--(ISNA(MATCH(A2:A10,A11:A12,0))),B2:B10)

Replies
10
Views
336
Replies
6
Views
64
Replies
3
Views
78
Replies
12
Views
148
Replies
7
Views
157

### Forum statistics

1,118,167
Messages
5,570,640
Members
412,334
Latest member
ExcelForLifeDontHate