VLOOKUP doesn't work in an array formula, apparently

larryk

New Member
Joined
Dec 30, 2005
Messages
3
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))}
 

Some videos you may like

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.

Brian from Maui

MrExcel MVP
Joined
Feb 16, 2002
Messages
8,458
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)
 

larryk

New Member
Joined
Dec 30, 2005
Messages
3
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
 

Watch MrExcel Video

Forum statistics

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