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.

 

[Jonmo1]

Try changing
"Comm"
to
Comm

And I highly recommend NOT using entire column references like A:A in that formula
Instead, restrict it to the actual used area like A$1:A$100

 

[kgardner]

When I remove the quotation marks around Comm in method B I still get #N/A.

Is the formula in method B wrong? Can a VBA code do what I am asking?

 

[Jonmo1]

Actually I think they're both wrong.
The first one returns a value, but I think it's coincidental
I don't think it's really doing what you think it is (or maybe I don't fully understand what you want it to do)..

I named the commercial products types (range A2:a10) "Comm"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)))}

So comm is a multicell range A2:A10
So your first formula translates to
{=INDEX('Data'!A:A, MATCH(A2:A10, 'Data'!C:C,0))}

What exactly is that supposed to be doing?
I don't think it does what you think it does..

 

[kgardner]

So comm is a multicell range A2:A10
So your first formula translates to
{=INDEX('Data'!A:A, MATCH(A2:A10, 'Data'!C:C,0))}

This is correct. I want excel to use the list of product types that are in cells A2:A10 and match them to the products in cells 'Data'!C:C to return the account numbers stored in 'Data'!A:A.

I have 2 product groups, Commercial & Consumer, each made up of multiple product types. I want all of the Commercial accounts to show on 1 worksheet and all the Consumer accounts to show on a separate worksheet. Maybe sample data will help?

A. Product Key Worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Commercial Product Types[/TD]
[TD]Consumer Product Types[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[/TR]
</tbody>[/TABLE]

B. Data worksheet
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account #[/TD]
[TD]Balance[/TD]
[TD]Product Type[/TD]
[/TR]
[TR]
[TD]784511321[/TD]
[TD]202.15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]321654678[/TD]
[TD]31.56[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]316584632[/TD]
[TD]784.00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]315654826[/TD]
[TD]9,351.00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

C. Commercial Worksheet (what I want the final product to be)
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Account #[/TD]
[TD]Balance[/TD]
[TD]Product Type[/TD]
[/TR]
[TR]
[TD]784511321[/TD]
[TD]202.15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]316584632[/TD]
[TD]784.00[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]315654826[/TD]
[TD]9,351.00[/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]


The formula I need help on would go into the Commercial Worksheet A2, A3, etc.

 

[Jonmo1]

ok, well you can't use a multicell range like that then, the lookup value in the MATCH needs to be a single cell.

Try this on the Commercial Worksheet, A2 and Filled Right/Down to B4

=INDEX('Data'!A:A,MATCH($C2,'Data'!$C:$C,0))

 

[kgardner]

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.

 

[Jonmo1]

So on the Commercial worksheet, you actually need the formula in column B to return the BALANCE.
You already have the account # in A, and product type in C ?

 

[kgardner]

Column B and column C are not causing me issues. I will use a simple vlookup or index match for those.

The problem I am having lies solely in column A of the Commercial worksheet where I want to list all of the account numbers from the Data worksheet that have a commercial product type, which are listed on the Product Type worksheet.

 

[Jonmo1]

OK, I think I get it now.

try

=IFERROR(INDEX(Data!A$2:A$5,SMALL(IF(ISNUMBER(MATCH(Data!C$2:C$5,Comm,0)),ROW(C$2:C$5)-ROW(C$2)+1),ROWS($A$2:A2))),"")