So you'll go from this:
To this with just a click:
I had no idea if this was possible, I don't exactly remember being taught this at school. So I figured I look into it. Challenge Accepted! After many a Google search I found nothing on this. So if there is a function for it, I haven't found it. So after about an hour of playing around with Excel functions, I came up with this:
SHEET = Name of the sheet containing the data
A = Cell containing data to compare to SHEET. (A1, B2, etc.)
B = Name of array holding the data on SHEET.
C = Column holding Data.
Here a sample if you want to see it work. temp.xlsx It simply looks at the Name column cell and compares it to a list in a already filled out table on SHEET, and prints out whatever is in C. The IF and ISERROR function are used to keep the cell clear in case of an error, makes it look prettier. Since leaving the Name cell empty would return an error.