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
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:
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):
<tbody>
</tbody>
WORKSHEET “PART” (headers of the following table are A1 to E1… so table range A1:E10):
<tbody>
</tbody>
Thanks in advance for your kind assistance.
Regards,
Juan
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
Code:
=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:
Code:
=OFFSET(Part!$A$1,1,0,COUNTA(Part!$A:$A),1)
Code:
=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