Hi There, I want to look 1 value and return the multiple results in different rows.

Hello Pete,

Thank you for your swift response, I have attached the work sheet.

just to re-frame my query, I have lots of names in column and each name has multiple contacts, so I want the contacts in different columns in one row for every name.

Thanks in advance,

Musavir Pasha.

Put this formula in D5:

Formula:

=B5&"_"&COUNTIF(B$5:B5,B5)

and copy down. Put this array* formula in F5:

Formula:

=IFERROR(INDEX($B$5:$B$19,MATCH(0,COUNTIF($F$4:F4,$B$5:$B$19),0)),"")

* Commit using Ctrl-Shift-Enter rather than the usual <Enter>

Then put this formula in G5:

Formula:

=IFERROR(INDEX($C$5:$C$19,MATCH($F5&"_"&COLUMNS($G:G),$D$5:$D$19,0)),"")

and copy across to K5, then copy F5:K5 down as far as you need.

The attached workbook illustrates this.

Hope this helps.

Pete

Good afternoon Pete,

You are Awesomeeeeeeeeeeee

One more small help, I have attached the sample file, what I need is the last contact of that person :

example : name 'A' has 2 contacts and 'B' has 5 and 'C' has 1 contact in same row, so I want the formula to pick the last contact only, like for A it should pick only 2nd contact, for B it should pick only 5th contact and C should pick only 1st contact.

Thank you in advance again

Cheers,
Musavir Pasha.

Put this formula in J3:

=LOOKUP(10E10,C3:G3)

then copy down.

Hope this helps.

Pete

Pete, you are supereb, thank you soo much.

Cheers,
Musa.

need conditional formatting.JPG

Hi Pete,

As per the image there are two tables and the cells are highlighted which are not matching, so is there a conditional formatting for this?

Thank you in advance,
Musavir Pasha.

Yes, but attach an Excel workbook so that I can put the formulae in for you - I said before that a picture isn't much use.

Pete