VLookup in Array Formula (XL2KSR1)

Topic

New Reply

I’m trying to use VLOOKUP in an array formula, however it doesn’t work right. My questions are: Can it be made to work, if so, how? if not, what alternative is there to this approach. Please see attachment

Thanks,
Ken

Reply | Quote

Replies

Ken

Did you try and use a Custom Made, User Defined VBA function for such a thing?

I think this would be your best best.

Let me know if you need any help with VBA, for now I’ll keep hacking at this until I find a Better solution for you.

Wassim
in the

Reply | Quote

ken
I suspect that it has become way too complex for easy analysis.
My assumption is that VLOOKUP decides that (even with array formulae) its job is to find the first item and then stop.

I had difficulty understanding why it had to be done in quite that way.
What was wrong with doing the VLOOKUPS in cells B8:B13 and then using the resulting single answers in a simple construct? It would certainly be easier to debug.

I enjoyed discovering your method of rangenaming CCs – nice one (I always wondered how to make robust definitions)

Andrew O

Reply | Quote

I agree with Andrew, and to detail what he alluded to:

In B8:B13 you would have the lookups:
=VLOOKUP(A8,CCs,2,0)
Name B8:B13 LookupMyList
and then use as ARRAY formula:
=SUM(IF(ISERROR(LookupMyList),0,IF(LookupMyList=$A$1,0,1)))

It adds an extra column, but that shouldn’t be a big deal.

You could even get rid of the array formula by using in B8:B13:
=VLOOKUP(A8,CCs,2,0)$A$1

and using the formula:
=COUNTIF(LookupMyList,TRUE)

I think that the problem is (as you noticed) that VLOOKUP is NOT an ARRAY formula. The command:
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Assumes that lookup_value is a single value. If it is a range, it uses the top-left value of the range. Even if this is ARRAY entered it ALWAYS and ONLY looks at the TopLeft value.

Steve

Reply | Quote

I added the following in cells B8:B13 (next to MyList range):

=IF(NOT(ISERROR(SEARCH(A$1,A8))),1,0) It seems to work OK. See attached file.

I could not get it to work as an array.

Brian

Reply | Quote

=SUMPRODUCT((ISNUMBER(MATCH(Table!A2:A84,MyList,0)))*(Table!B2:B84=A1))

to be entered in A3 in Sheet1.

Aladin

Reply | Quote

Thanks to all who answered my post. Adding an additional column is not an option that I wish to use because this example is cut down from a form that I use. To start adding column(s) would require a lot of reworking of macros and the form itself (although I suppose I could use a hidden column). Anyway Aladin nailed it for me. Thanks Aladin.

Ken

Reply | Quote