array match

I attempted to modify the below formula to encompass more than the 13 rows (See original formula below): Originally Posted by Eric W H4: Code: =SUMPRODUCT(--(MATCH(B2:B13,B2:B13,0)=ROW(B2:B13)-ROW(B2)+1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"DA")=1),--(COUNTIFS(B2:B13,B2:B13,C2:C13,"THER...

Hi all, I have an array formula that finds the minimum difference between a value (stored in E5) and a 2D array of values (stored in another sheet, Database2!B5:U25). The array formula I used is as follows (entered with CTRL-SHIFT-ENTER): =MIN(ABS(Database2!$B$5:$U$25-E5)) This gives me the...

I have a table that has suppliers identified on the 1st row. The cells in the table specify the goods the suppliers can provide. i.e Column 1 - Supplier A, Bread, Butter, Crisps, Cheese Column 2 - Supplier B, Crisps Column 3 - Supplier C, Cheese, Fruit, Bread I have created a drop down list of...

Hello There, I have come up with a real big problem. Initially i took help from Bob Umlas author of the books "it isnt excel its magic", and he has provided me with a really good solution. But the solution persists for fewer data only. whenever i am trying to apply it in a large table then a...

What is the simplest way to get an array formula to return the integer that represents its index? IE, I have an array {John,apple,4,TRUE,7} and I want {1,2,3,4,5} to be the result. Previously, I had been using MATCH(RANGE,RANGE) to obtain this but it backfired when some of the elements in the...