array match

  1. B

    Getting #N/A Error for Array Formula (Entire Column)

    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...
  2. P

    Find a value within a 2D array

    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...
  3. M

    Multiple Look up returns header values

    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...
  4. H

    Lookup the closest match

    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...
  5. Phox

    Nth term of an array formula

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

Some videos you may like

This Week's Hot Topics