Thread: Using Vlookup & Data Validation

Hello,

I am trying to list of contact details for a group. let say if i select a group Bilad & Merad it should list all the hospital and clinic belongs to this group.

any idea how to do this, please help me.

Thanks in advance
Best Regards

In B18, try:

=IFERROR(INDEX(Database!$A$2:$A$500,SMALL(IF(Database!C2:C500=$D$3,ROW(Database!$A$2:$A$500)-ROW(Database!$A$2)+1),ROWS($B$18:$B18))),"")

confirmed with CTRL+SHIFT+ENTER and copied down.

The other VLOOKUPs in the other columns should be okay.

I neglected to make one range absolute...

try:

=IFERROR(INDEX(Database!$A$2:$A$500,SMALL(IF(Database!$C$2:$C$500=$D$3,ROW(Database!$A$2:$A$500)-ROW(Database!$A$2)+1),ROWS($B$18:$B18))),"")

confirmed with CTRL+SHIFT+ENTER.

If you want to ignore the Aleem from row 2, then start all ranges in above formula at row 3....ie replace $2 with $3

Thank you very much i really appreciated your help. God bless you friend.

their is small error. if i leave d3 empty it shows #N/A
#N/A
#N/A
in each cell

any solution for this.

Is this non-array solution what you are looking for?

I have rearranged your sheet a little to make it easier to maintain and also make it dynamic.

Your database should be sorted on "Provider Group" also I removed what appeared a redundant entry.

Select from the drop-down in C3

Originally Posted by hecgroups

Thank you very much i really appreciated your help. God bless you friend.

their is small error. if i leave d3 empty it shows #N/A
#N/A
#N/A
in each cell

any solution for this.

You can precede the formula with an IF check:

=IF($D$3="","",IFERROR(INDEX(Database!$A$2:$A$500,SMALL(IF(Database!$C$2:$C$500=$D$3,ROW(Database!$A$2:$A$500)-ROW(Database!$A$2)+1),ROWS($B$18:$B18))),""))