Formula to compare more than 2 List using Match

[Saran]

Hi,

I'm trying to write a output as "Cy1", "Cy2", "Cy3", "Cy4", etc.... Using the formula from various Array value of A2:A6 and B2:B6 etc....

=IF(AND(MATCH(G2,$A$2:$A$6,0)),"Cy1",

All I want to achieve is If "G2" matches with A2:A10 then I want to Write in H2 as "Cy1", and similarly If "G2" matches with B2:B10 then I want to Write in H2 as "Cy1" and so on for minimum until column "F".

Thanks,
S.Saran

 

[Somendra Misra]

Hi Saran,

If you can explain the same with the help of sample file, it will be helpful for those who wants to help you.

Meantime see the attached file. Are you after something like this.

Regards,
Somendra Misra

 

[Saran]

more or less similar one, but when I copy this formula and past onto my workbook, it is returning it as "Not Found"

may be I'm missing the Trick of usage of the {}. Should I use {} with Ctrl+Shift+Enter. here is my file.

 

[Saran]

Also, let how do handle the duplicate values, If you see my Excel (attached) there will be duplicate. Worst case I would like to populate the last found cycle name for the duplicate entry.

Thanks,
S.Saran

 

[Somendra Misra]

@Saran

See the attached file, pink cell, yes this is an array formula, so must be entered with Ctrl+Shift+Enter.

Regards,

 

[Saran]

Thanks a Lot, I think other than handling the Duplicate data, rest all is working as of now. Thanks.

 

[Somendra Misra]

@Saran

The file I uploaded in comment#5 is handling dups. and as per your request returning the last cycle. I had assume Cycle 6 is last and cycle 1 is first.

Regards,

 

[Saran]

Thanks a Lot that saved so lot of my time.