Substitute(Aconcant(IF...... formula

[Renee_KK]

=SUBSTITUTE(aconcat(IF($AH$3:$AH$22=AN10,", "&$AG$3:$AG$22,"")),", ","",1)

Hi there
I am using the above formula which has worked perfectly in the past and now for some reason it os only returning #VALUE! as the result.
Any ideas on what is causing this please?
Many thanks
Renee

 

[btorrean]

What is 'aconcat'? When I paste your formula into a blank workbook, it seems to think that aconcat is a name of some type.

Brian

Brian J. Torreano

 

[StephenCrump]

Check your cells for error values, e.g. perhaps you have a #N/A value somewhere in

- Cell AN10, or
- Cells AH3:AH22, or
- The cells AG3:AG22 where you have the match AH3:AH22=AN10?

 

[Renee_KK]

Check your cells for error values, e.g. perhaps you have a #N/A value somewhere in

- Cell AN10, or
- Cells AH3:AH22, or
- The cells AG3:AG22 where you have the match AH3:AH22=AN10?

Thank you Stephen! You were exactly right - there was an #N/A in column AH (which was using a VLOOKUP) which i corrected with a IF(ISNA(VLOOKUP and now it working again. Many thanks for your help.

 

[StephenCrump]

Great, glad you got it working.