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

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top