INDEX / MATCH with the LOOKUP_ARRAY for the MATCH variable. Uses INDIRECT

[excells]

Hi all,

I’m trying to find data in several sheets using INDEX / MATCH. I get the first value right but all the others are “#N/A”.

The data is in different arrays, each array in a different worksheet. So, the LOOKUP_ARRAY for the MATCH varies.

For example, on Worksheet “BOQ” below, I want to retrieve the Cost/Unit for Item1. The information of Item1 is on another worksheet called “PART”

On cell C2 of the worksheet “BOQ” I am using the following expression

=INDEX(PART_COST,(MATCH(B2,OFFSET(INDIRECT(A2&"!$A$1"),1,0,COUNTA(INDIRECT(A2&"!&$A:$A")),1),0)))

Which gives me the value of $500. Which is right.

PART_COST is a dynamic range (with 1 only column) on WORKSHEET “PART”. PART_COST is an offset of PART_ID, as follows:

=OFFSET(Part!$A$1,1,0,COUNTA(Part!$A:$A),1)

=OFFSET(PART_ID,0,4)

THE PROBLEM I HAVE IS: the first value found ($500) below is ok but as I drag the formula down all I get is “#N/A”

WORKSHEET “BOQ” (headers of the following table are A1 to C1… so table range A1:C10):
[TABLE="width: 213"]
<tbody>[TR]
[TD]Section
[/TD]
[TD]Designation
[/TD]
[TD]Cost/Unit
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item1
[/TD]
[TD]$500
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item2
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item3
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item4
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item5
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item6
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item7
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item8
[/TD]
[TD]#N/A
[/TD]
[/TR]
[TR]
[TD]Part
[/TD]
[TD]Item9
[/TD]
[TD]#N/A
[/TD]
[/TR]
</tbody>[/TABLE]


WORKSHEET “PART” (headers of the following table are A1 to E1… so table range A1:E10):
[TABLE="width: 369"]
<tbody>[TR]
[TD]Designation
[/TD]
[TD]Colour
[/TD]
[TD]Size
[/TD]
[TD]Unit
[/TD]
[TD]Mat cost/unit
[/TD]
[/TR]
[TR]
[TD]Item1
[/TD]
[TD]red
[/TD]
[TD]large
[/TD]
[TD]ea
[/TD]
[TD] $ 500.00
[/TD]
[/TR]
[TR]
[TD]Item2
[/TD]
[TD]blue
[/TD]
[TD]smal
[/TD]
[TD]ea
[/TD]
[TD] $ 2,000.00
[/TD]
[/TR]
[TR]
[TD]Item3
[/TD]
[TD]green
[/TD]
[TD]medium
[/TD]
[TD]ea
[/TD]
[TD] $ 3,500.00
[/TD]
[/TR]
[TR]
[TD]Item4
[/TD]
[TD]red
[/TD]
[TD]large
[/TD]
[TD]ea
[/TD]
[TD] $ 5,000.00
[/TD]
[/TR]
[TR]
[TD]Item5
[/TD]
[TD]black
[/TD]
[TD]medium
[/TD]
[TD]ea
[/TD]
[TD] $ 3,400.00
[/TD]
[/TR]
[TR]
[TD]Item6
[/TD]
[TD]black
[/TD]
[TD]small
[/TD]
[TD]ea
[/TD]
[TD] $ 4,322.00
[/TD]
[/TR]
[TR]
[TD]Item7
[/TD]
[TD]blue
[/TD]
[TD]large
[/TD]
[TD]ea
[/TD]
[TD] $ 2,345.00
[/TD]
[/TR]
[TR]
[TD]Item8
[/TD]
[TD]white
[/TD]
[TD]medium
[/TD]
[TD]ea
[/TD]
[TD] $ 3,300.00
[/TD]
[/TR]
[TR]
[TD]Item9
[/TD]
[TD]purple
[/TD]
[TD]standard
[/TD]
[TD]ea
[/TD]
[TD] $ 1,234.00
[/TD]
[/TR]
</tbody>[/TABLE]

Thanks in advance for your kind assistance.

Regards,
Juan

 

[CharlesChuckieCharles]

your INDIRECT(A2 should that not be INDIRECT($A$2

 

[excells]

Hi CharlesChuckieCharles,
Thanks for giving it a thought.
It needs to be A2 because it needs to change from row to row, to tell the expression which worksheet is the one where the range of data will be found. In the example I use the worksheet is 'PART' but it could really be other worksheet.

I, however, just tested it with $A$2 but still does not work. keeps on returning #N/A for all values except the first one.

Would you have any other ideas?

Cheers,
Juan

 

[circledchicken]

Hi,

You seem to have an unnecessary ampersand in your formula in C2.
Perhaps try it like this instead:

=INDEX(
    PART_COST,
    MATCH(
      B2,
      OFFSET(INDIRECT(A2 & "!$A$1"), 1, 0, COUNTA(INDIRECT(A2 & "!$A:$A")), 1),
      0))

However, since you have already defined PART_ID as well, you could shorten it using that to something like:

=INDEX(
    PART_COST,
    MATCH(
      B2,
      PART_ID,
      0))

Also, just a note that your named ranges contain an extra row because the header is also counted. You could change that with something like:

=OFFSET(Part!$A$1, 1, 0,COUNTA(Part!$A:$A) - 1, 1)

 

[excells]

Hi,
I checked and the expression that isn't working is:


=MATCH(B2,OFFSET(INDIRECT(A2&"!$A$1"),1,0,COUNTA(INDIRECT(A2&"!&$A:$A")),1),0)
It finds the position of the first item (the one on cell B2) correctly. But when I drag the formula down to the rest of the items on the worksheet I get #N/A.

Is there a problem with the way Im writting the lookup_array? Can't it be a dynamic range as I have it? or is it the use of INDIRECT? Why does it work for the first item but not subsequent?


if you want to test it have to sheets, "boq" and "part"
Paste the following on A1 "boq" and A1 "part" correspondingly
[TABLE="width: 209"]
<tbody>[TR]
[TD]Section[/TD]
[TD]Designation[/TD]
[TD]match[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item2[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item3[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item4[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item5[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item6[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item7[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item8[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD]Part[/TD]
[TD]Item9[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]


this expression goes on the column "match"
=MATCH(B2,OFFSET(INDIRECT(A2&"!$A$1"),1,0,COUNTA(INDIRECT(A2&"!&$A:$A")),1),0)



[TABLE="width: 328"]
<tbody>[TR]
[TD]Designation[/TD]
[TD]Colour[/TD]
[TD]Size[/TD]
[TD]Unit[/TD]
[TD]Mat cost/unit[/TD]
[/TR]
[TR]
[TD]Item1[/TD]
[TD]red[/TD]
[TD]large[/TD]
[TD]ea[/TD]
[TD] $ 500.00[/TD]
[/TR]
[TR]
[TD]Item2[/TD]
[TD]blue[/TD]
[TD]smal[/TD]
[TD]ea[/TD]
[TD] $ 2,000.00[/TD]
[/TR]
[TR]
[TD]Item3[/TD]
[TD]green[/TD]
[TD]medium[/TD]
[TD]ea[/TD]
[TD] $ 3,500.00[/TD]
[/TR]
[TR]
[TD]Item4[/TD]
[TD]red[/TD]
[TD]large[/TD]
[TD]ea[/TD]
[TD] $ 5,000.00[/TD]
[/TR]
[TR]
[TD]Item5[/TD]
[TD]black[/TD]
[TD]medium[/TD]
[TD]ea[/TD]
[TD] $ 3,400.00[/TD]
[/TR]
[TR]
[TD]Item6[/TD]
[TD]black[/TD]
[TD]small[/TD]
[TD]ea[/TD]
[TD] $ 4,322.00[/TD]
[/TR]
[TR]
[TD]Item7[/TD]
[TD]blue[/TD]
[TD]large[/TD]
[TD]ea[/TD]
[TD] $ 2,345.00[/TD]
[/TR]
[TR]
[TD]Item8[/TD]
[TD]white[/TD]
[TD]medium[/TD]
[TD]ea[/TD]
[TD] $ 3,300.00[/TD]
[/TR]
[TR]
[TD]Item9[/TD]
[TD]purple[/TD]
[TD]standard[/TD]
[TD]ea[/TD]
[TD] $ 1,234.00[/TD]
[/TR]
</tbody>[/TABLE]


Your help is appreciated.

Juan

 

[excells]

Hi circledchicken,
I deleted the extra ampersand and it worked like a charm.
Thanks so much for looking into it.

Have a good day
Juan

 

[excells]

Hi,

You seem to have an unnecessary ampersand in your formula in C2.
Perhaps try it like this instead:

=INDEX(
    PART_COST,
    MATCH(
      B2,
      OFFSET(INDIRECT(A2 & "!$A$1"), 1, 0, COUNTA(INDIRECT(A2 & "!$A:$A")), 1),
      0))

However, since you have already defined PART_ID as well, you could shorten it using that to something like:

=INDEX(
    PART_COST,
    MATCH(
      B2,
      PART_ID,
      0))

Also, just a note that your named ranges contain an extra row because the header is also counted. You could change that with something like:

=OFFSET(Part!$A$1, 1, 0,COUNTA(Part!$A:$A) - 1, 1)

Thanks Circledchicken, it worked. Brilliant

 

[circledchicken]

You're welcome - thanks for the feedback!

 

[XYZ1234]

Dear Madam/Sir,
Kindly advise,
[TABLE="width: 688"]
<tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mango[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Jackfroot[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pears[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jackfroot[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Formula in D1:D5[/TD]
[TD="colspan: 5"] =LARGE($a$1:$a$6,C1)[/TD]
[/TR]
[TR]
[TD]Formula in E1:E5[/TD]
[TD="colspan: 5"] =INDEX($D$15:$D$20,MATCH(G15,$E$15:$E$20,0))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Problem[/TD]
[TD="colspan: 5"]Expected result in coloumn E should be as mentioned below, Kindly advise what is wrong or which function to be used.[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]Orange[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mango[/TD]
[TD]2[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]Jackfroot[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Banana[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]Mango[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Pears[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]2[/TD]
[TD]Banana[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Jackfroot[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]2[/TD]
[TD]Pears[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Orange[/TD]
[TD]4[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Thanks & Regards[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="4"><col span="2"></colgroup>[/TABLE]