Formula LOOKUP returns incorrect value

[Annalise19]

I have a list of names and want to find each person's designation from a database, but LOOKUP won't give me the correct value


This is my formula: =LOOKUP(A1646,A3:A3514,I3:I3514)

Col A - Name
Col I - Designation

Please note that the names are not repeated. How could I make it right?

 

[AhoyNC]

Are the names sorted in ascending order (they need to be to use LOOKUP)?
If not try VLOOKUP.

=VLOOKUP(A1646,$A$3:$A$3514,9,0)

 

[Annalise19]

Are the names sorted in ascending order (they need to be to use LOOKUP)?
If not try VLOOKUP.

=VLOOKUP(A1646,$A$3:$A$3514,9,0)

Thank you for your reply! You're right the names are not in order!

I'm having problem with VLOOK function too...
=VLOOKUP(A3826,$A$3:$A$3514,9,FALSE)

(The list of names are at the bottom of the same sheet)

 

[Annalise19]

The cell shows:

#REF!

<tbody>
</tbody>

 

[Gerhardoo]

Hi Annalise, hope this is not frustrating you too much.

Due to the way that VLOOKUP works, I would advise that you move the list of names to a new sheet starting at A1 - go back to the sheet with the data, click somewhere within the data and use CTRL + T to select the range of continuous cells and click ok to make it into a table if it isn't already and give it a Table name. Once you have done that, try using VLOOKUP again. I assume you are rather a novice, so if you have trouble from there on, reply here and we will help you further

 

[AhoyNC]

This part of the VLOOKUP has to reference the whole table range. In your post you showed column I has having the data you wanted to return. So, your formula would need to be
=VLOOKUP(A3826,$A$3:$I$3514,9,FALSE)
Change to match your actual data if needed. The 9 is the column number to return.

 

[Annalise19]

Hi Annalise, hope this is not frustrating you too much.

Due to the way that VLOOKUP works, I would advise that you move the list of names to a new sheet starting at A1 - go back to the sheet with the data, click somewhere within the data and use CTRL + T to select the range of continuous cells and click ok to make it into a table if it isn't already and give it a Table name. Once you have done that, try using VLOOKUP again. I assume you are rather a novice, so if you have trouble from there on, reply here and we will help you further

This part of the VLOOKUP has to reference the whole table range. In your post you showed column I has having the data you wanted to return. So, your formula would need to be
=VLOOKUP(A3826,$A$3:$I$3514,9,FALSE)
Change to match your actual data if needed. The 9 is the column number to return.

OMG I'm super duper thankful! That saves me tons of trouble!!
Thank you all and have a great day! :D

 

[Annalise19]

 

[T Crzu]

=VLOOKUP(A1646,$A:$I,9,0)

This is abit weird, because you are using the array to find within the array, which you could already find in column I. Did you have another tab that you search for the Name, which is your A1646.