Returning multiple values for one lookup

[BurkeDad]

I am trying to create an array formula that returns multiple values - in a row- from a single look value - with only one criteria.

Here is the formula I have sa far - =IF(ROWS(A$67:A67)<=$A$63,INDEX('Individual Facility Information'!$A$5:$A$1145,SMALL(IF('Individual Facility Information'!$G$5:$G$1145=$F$7,ROW('Individual Facility Information'!$G$5:$G$1145)-ROW('Individual Facility Information'!$G$5)+1),ROWS(A$67:A67))),"")

When I hit crtl, shift, enter - it retuns a value of #NUM!. Mr. Excel has this on his youtube page - followed the same logic......

 

[Marcelo Branco]

Hi.

Hmmm..the formula seems OK.

Maybe you have leading or trailing spaces that are causing the comparison
IF('Individual Facility Information'!$G$5:$G$1145=$F$7...
to fail

Just in case, try changing this part to
IF(TRIM('Individual Facility Information'!$G$5:$G$1145)=TRIM($F$7)...

HTH

M.

 

[T. Valko]

I am trying to create an array formula that returns multiple values - in a row- from a single look value - with only one criteria.

Here is the formula I have sa far - =IF(ROWS(A$67:A67)<=$A$63,INDEX('Individual Facility Information'!$A$5:$A$1145,SMALL(IF('Individual Facility Information'!$G$5:$G$1145=$F$7,ROW('Individual Facility Information'!$G$5:$G$1145)-ROW('Individual Facility Information'!$G$5)+1),ROWS(A$67:A67))),"")

When I hit crtl, shift, enter - it retuns a value of #NUM!. Mr. Excel has this on his youtube page - followed the same logic......

See this...

http://www.mrexcel.com/forum/showpost.php?p=2696961&postcount=3

 

[BurkeDad]

Marcelo - you hit the nail right on the head............. I love it. It always seems to be a little thing............

Thanks

 

[Marcelo Branco]

Marcelo - you hit the nail right on the head............. I love it. It always seems to be a little thing............

Thanks

You are welcome and tks for the feedback

M.