Formula LOOKUP returns incorrect value

Annalise19

New Member
Joined
Nov 2, 2016
Messages
8
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?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,724
Office Version
  1. 365
Platform
  1. Windows
Are the names sorted in ascending order (they need to be to use LOOKUP)?
If not try VLOOKUP.
Code:
=VLOOKUP(A1646,$A$3:$A$3514,9,0)
 

Annalise19

New Member
Joined
Nov 2, 2016
Messages
8
Are the names sorted in ascending order (they need to be to use LOOKUP)?
If not try VLOOKUP.
Code:
=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)
 

Gerhardoo

New Member
Joined
Nov 3, 2016
Messages
6

ADVERTISEMENT

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

Well-known Member
Joined
Oct 10, 2011
Messages
4,724
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 2, 2016
Messages
8

ADVERTISEMENT

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
 

T Crzu

New Member
Joined
Oct 31, 2016
Messages
7
=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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,369
Messages
5,601,223
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top