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):

Section	Designation	Cost/Unit
Part	Item1	$500
Part	Item2	#N/A
Part	Item3	#N/A
Part	Item4	#N/A
Part	Item5	#N/A
Part	Item6	#N/A
Part	Item7	#N/A
Part	Item8	#N/A
Part	Item9	#N/A

<tbody>
</tbody>

WORKSHEET “PART” (headers of the following table are A1 to E1… so table range A1:E10):

Designation	Colour	Size	Unit	Mat cost/unit
Item1	red	large	ea	$ 500.00
Item2	blue	smal	ea	$ 2,000.00
Item3	green	medium	ea	$ 3,500.00
Item4	red	large	ea	$ 5,000.00
Item5	black	medium	ea	$ 3,400.00
Item6	black	small	ea	$ 4,322.00
Item7	blue	large	ea	$ 2,345.00
Item8	white	medium	ea	$ 3,300.00
Item9	purple	standard	ea	$ 1,234.00

<tbody>
</tbody>

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

Section	Designation	match
Part	Item1	1
Part	Item2	#N/A
Part	Item3	#N/A
Part	Item4	#N/A
Part	Item5	#N/A
Part	Item6	#N/A
Part	Item7	#N/A
Part	Item8	#N/A
Part	Item9	#N/A

<tbody>
</tbody>

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

Designation	Colour	Size	Unit	Mat cost/unit
Item1	red	large	ea	$ 500.00
Item2	blue	smal	ea	$ 2,000.00
Item3	green	medium	ea	$ 3,500.00
Item4	red	large	ea	$ 5,000.00
Item5	black	medium	ea	$ 3,400.00
Item6	black	small	ea	$ 4,322.00
Item7	blue	large	ea	$ 2,345.00
Item8	white	medium	ea	$ 3,300.00
Item9	purple	standard	ea	$ 1,234.00

<tbody>
</tbody>

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,

	A	B	C	D	E
1	Apple	1	1	4	Orange
2	Mango	2	2	3	Jackfroot
3	Banana	2	3	2	Mango
4	Pears	2	4	2	Mango
5	Jackfroot	3	5	2	Mango
6	Orange	4
Formula in D1:D5	=LARGE($a$1:$a$6,C1)
Formula in E1:E5	=INDEX($D$15:$D$20,MATCH(G15,$E$15:$E$20,0))
Problem	Expected result in coloumn E should be as mentioned below, Kindly advise what is wrong or which function to be used.
	A	B	C	D	E
1	Apple	1	1	4	Orange
2	Mango	2	2	3	Jackfroot
3	Banana	2	3	2	Mango
4	Pears	2	4	2	Banana
5	Jackfroot	3	5	2	Pears
6	Orange	4
Thanks & Regards

<tbody>
</tbody><colgroup><col span="4"><col span="2"></colgroup>