Using Named Ranges as the LOOKUP_VALUE in Index-Match Formula

[kgardner]

Hello All,

I have a database of information with account numbers, product types and balances. Each account number has only 1 product type and the balance changes daily. I have a "product key" which I have listed the different product categories separated by the product types that make them up (i.e. Commercial = 1, 2, 3, 4.. & Consumer = 11, 12, 13, 14...). I named the commercial products types (range A2:a10) "Comm" and the consumer product types (range b2:b10) "Consumer."

I want to transfer all of the Commercial accounts from the primary database (worksheet "Data") to a separate worksheet (worksheet "Commercial") and for this to update automatically upon refresh. This data is no available through access, I have to import from a outside database to excel and then sort.

I have tried many different look-up functions and all have failed, including:

A. This method produces 1 correct result and then repeats

{=INDEX('Data'!A:A, MATCH(Comm, 'Data'!C:C,0))}

B. This method produces #N/A

 {=INDEX('Data'!A:A, SMALL(IF('Data'!C:C = "Comm", ROW('Data'!C:C)), ROW('$A$2:A2)))}

To simplify my request: I need to look up the account numbers from a database that match a specific product group, which is made up of a list of possible product types.

 

[kgardner]

That formula only works for the 1st account number. It is not returning the 2nd and 3rd account number.

 

[Jonmo1]

Works for me

Make sure you're entering it in A2 as an array with CTRL + SHIFT + ENTER
Then Fill down.

 

[kgardner]

It worked! Thank you for your assistance and your time.

 

[Jonmo1]

You're welcome.

 

[bddoss22]

That solution only scratches the surface. I have 20,000+ accounts with unique account numbers and repeating product types. The index-match formula as you write it only returns the 1st match between the 'Data' worksheet and 'Product Key' worksheet as well as numerous #N/A values from accounts with consumer product types.

Is there a way for excel to use multiple criteria in a lookup formula the same way you can use multiple criteria in a sumproduct or sumif formula? Again, I am somewhat familiar with VBA if that is viable.

I have just discovered the use of multiple criteria in Index/match statements. Basically, you concatenate your match criteria and arrays with "&". These have to be executed as an array, though, using Ctrl+Shift+Enter to calculate. For example, here's one of mine that matches two values from two different columns as the first Match statement (to define the row of Index), then a single Match criteria and range for the column-portion of index:

={IFERROR((INDEX($B$2:$AZ$5001,MATCH("Y"&$AA2,$I$2:$I$5001&$AA$2:$AA$5001,0),MATCH("Test EFT (h)",$B$1:$AZ$1,0))),"")}

The curly brackets get applied on execution.

 

[bddoss22]

I have just discovered the use of multiple criteria in Index/match statements. Basically, you concatenate your match criteria and arrays with "&". These have to be executed as an array, though, using Ctrl+Shift+Enter to calculate. For example, here's one of mine that matches two values from two different columns as the first Match statement (to define the row of Index), then a single Match criteria and range for the column-portion of index:

={IFERROR((INDEX($B$2:$AZ$5001,MATCH("Y"&$AA2,$I$2:$I$5001&$AA$2:$AA$5001,0),MATCH("Test EFT (h)",$B$1:$AZ$1,0))),"")}

The curly brackets get applied on execution.