• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using INDEX and MATCH but creating a message to indicate absence

This is my first use of this website and when I found it I thought how good it was. Here is my problem.

I have been using INDEX & MATCH but struggling to get the syntax for the following scenario. Simplifying will enable my problem to be explained better.


I have a list of names in an Excel spreadsheet from , say A10:A20. They are not in alphabetical order.

I have one cell , say A1 , into which a name is entered. I want to look through the list of names to see if I can find the one that I have just entered and in cell , say , A5 put the located name or , if it does not appear in the list , put the message "Name not here"


Can someone please tell me what the expression should be ?
 
If you just want to check whether name is there or not, just use the below formula at B1 as and type the name at A1


'=IF(ISNA(MATCH(A1,$A$10:$A$20,0)),"Name not here","Name found at "& MATCH(A1,$A$10:$A$20,0))
 
Thanks for that ! I have used your reply and adapted the use of INDEX with it to give me what I was after.


Thanks again


Terry Dennis
 
Back
Top