Question regarding Indirect use in formula

[cddeboer]

Hi-
I have an array formula as follows:
=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(INDIRECT("$A"& ROW()),IF(PlateSampleInfo!$C$8:$C$65000=E2,PlateSampleInfo!$A$8:$A$65000),0))

However, if I try to replace the "E2" in the formula with INDIRECT("$E"& ROW()), it doesn't work.

Does anybody know why? Or what I might need to do differently?
Thanks

[Jonmo1]

Can you describe "it doesn't work" ?

Do you get an error? What error?
Do you get the wrong results? What results DID you get, and how are they different from what you expected?

Can you post the actual formula you tried where "it doesn't work" ?

[cddeboer]

The formula that works is:
=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(INDIRECT("$A"& ROW()),IF(PlateSampleInfo!$C$8:$C$65000=E2,PlateSampleInfo!$A$8:$A$65000),0))
The formula that doesn't work returns #N/A and is shown below.
=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(INDIRECT("$A"& ROW()),IF(PlateSampleInfo!$C$8:$C$65000=Indirect("E" & row()),PlateSampleInfo!$A$8:$A$65000),0))

[FDibbins]

is the formula in row 2? because if you are in row 8, then =Indirect("E" & row() will give you E8

[cddeboer]

Yes. The formula is in row 2. The indirect function works in the earlier part of the formula when I am referring to cell A2 and have replaced it with INDIRECT("$A"&ROW()), but it doesn't work when I replace E2 with INDIRECT("E"&ROW()) or INDIRECT("$E"&ROW())

[Jonmo1]

Can you post a sample book with examples of both working and non working formulas ?

[Jonmo1]

I think it's because you're doing an array function.

And the row function doesn't work in the IF part of that MATCH(IF array
Because ROW doesn't return an array...
Try the ROWS function instead

=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(INDIRECT("A"&ROW()),IF(PlateSampleInfo!$C$8:$C$65000=INDIRECT("E"&ROWS(A$1:A2)),PlateSampleInfo!$A$8:$A$65000),0))



Also, why are you even using Indirect to begin with?
Why not just

=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(A2,IF(PlateSampleInfo!$C$8:$C$65000=E2,PlateSampleInfo!$A$8:$A$65000),0))

[cddeboer]

I think it's because you're doing an array function.

And the row function doesn't work in the IF part of that MATCH(IF array
Because ROW doesn't return an array...
Try the ROWS function instead

=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(INDIRECT("A"&ROW()),IF(PlateSampleInfo!$C$8:$C$65000=INDIRECT("E"&ROWS(A$1:A2)),PlateSampleInfo!$A$8:$A$65000),0))



Also, why are you even using Indirect to begin with?
Why not just

=INDEX(PlateSampleInfo!$D$8:$D$65000,MATCH(A2,IF(PlateSampleInfo!$C$8:$C$65000=E2,PlateSampleInfo!$A$8:$A$65000),0))

In the Match part of the formula, it is looking for a match of the value in a single cell(E2) within an array.
I wanted to create the formulas within VBA and just use the row() function within the formula so that I didn't have to code in the actual row number of the cell.

[Jonmo1]

It appears this is more difficult than just coding the row into the formula.

What method were you planning to use to determine which row the formula goes into?
You can use the same method to put the same row # into the formula.