Indirect in array formula

[westend9876]

Can anyone help me get this formula to work
=IFERROR(INDEX(Sold!$BV$2:INDIRECT(DG2),SMALL(IF($M2&$U2&$V2&$L2=Sold!$M$2:$M$4174&Sold!$U$2:$U$4174&Sold!$V$2:$V$4174&Sold!$L$2:$L$4174,ROW(Sold!$BV$2:INDIRECT(DG2))-1,""),COLUMN(A1))),"")

Cell DG2 contains the text BV4174. So my range is $BV$2:BV4174

Thanks

 

[Faseeh]

westend,

I just tried this formula can you point out what is wrong with it?

 

[NARAYANK991]

Hi ,

Try this :

=IFERROR(INDEX(Sold!$BV$2:INDIRECT("Sold"&"!"&DG2),SMALL(IF($M2&$U2&$V2&$L2=Sold!$M$2:$M$4174&Sold!$U$2:$U$4174&Sold!$V$2:$V$4174&Sold!$L$2:$L$4174,ROW(Sold!$BV$2:INDIRECT("Sold"&"!"&DG2))-1,""),COLUMN(A1))),"")

entered as an array formula , using CTRL SHIFT ENTER.

You need to use the Sheet name in the INDIRECT , since otherwise the INDIRECT will not use the proper reference.

Narayan

 

[Xiq]

Hi West,

It would be very helpful if you give us a sample file and an example of the expected result.

But to start of:

• The COLUMN(A1) returns always 1 if you copy the complete formula horizontal. Looking at your formula, I guess this is indeed the case. If so, maybe change it to ROW(A1).
• You use a dynamic range for your INDEX function (via the INDIRECT function) to return a result, but not "match" part of the formula. I would guess this can give problems / incorrect results.
• You request the (lowest) row number of the "match". So lets say it finds a match at the top of the array (BV2), this will result in ROW(BV2) equals 2. The index then looks for the 2nd piece of information in BV2:BV4174, so in BV3... I guess that's not what you want.

 

[Xiq]

Hi West,

W/o any sample data, I don't know if it works, but try this:

=IFERROR(INDEX(Sold!$BV$2:$BV$4174,MATCH($M2&$U2&$V2&$L2,Sold!$M$2:$M$4174&Sold!$U$2:$U$4174&Sold!$V$2:$V$4174&Sold!$L$2:$L$4174,)),"")

*I removed the indirect function for now, but I trust you can replace it yourself

 

[westend9876]

@Xiq
The formula works just fine by entering the cell address of the last column, in this case $BV$4174. However, the problem is that the data in this column will change frequently so the last cell could be anything, hence my purpose of the INDIRECT reference to $DG$2. This cell refers to the last cell of data in the column Sold!BV. Therein lies my problem. I cannot get the array formula to work with the INDIRECT function. I really need to replace all instances of Row 4174 in the formula with the Indirect reference to the cell.

@Narayan
I tried your formula as well, using the Sheet name in the Indirect but it too did not work.

 

[NARAYANK991]

Hi ,

Can you say what happened ? What was the result ?

It would help if you could upload your file. I am uploading an example file ; see why your formula does not work when it does in this file.

Narayan

 

[westend9876]

Hi ,

Can you say what happened ? What was the result ?

It would help if you could upload your file. I am uploading an example file ; see why your formula does not work when it does in this file.

Narayan

Hi Narayan. I tried your formula in the attached worksheet and it works just fine. I do not know what occurred on my original pass. Thank you kindly.