# Lookup Formula

#### ClaireK

##### New Member
Hello i am a beginner with functions and would like some help please.
I would like my cell to look up a number from a column and return the text that is in the adjacent column relative to that number.

### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Richard Schollar

##### MrExcel MVP
Hi Claire

Check out Vlookup in Excel Help. An example of using this would be:

Code:
``=VLOOKUP(A1,Sheet2!\$A\$1:\$C\$100,3,0)``

which is telling Excel to look up the value in cell A1 of the current sheet (could be a name like Fred or a number) in the A column of sheet2 (cells 1:100) and return the contents of the 3rd column on the same line (ie the C column). The 0 on the end of the vlookup specifies an exact match.

#### NextWorldAngel

##### New Member
I'm trying to use this same code, and I keep getting a REF# error. Any thoughts?

#### Richard Schollar

##### MrExcel MVP
Hi NextWorldAngel

Can you provide the exact formula you are using (copy and paste it in) and also provide details of where your lookup range resides and from which column you wish to extract data.

Thanks

#### NextWorldAngel

##### New Member

=VLOOKUP(D8,A15:A240,2)

D8 is a formula that is pulling a number.
I need to find that number in range A15:A20 and pull the name that is 2 columns (Column C) from Column A.

#### Richard Schollar

##### MrExcel MVP
Your lookup range needs to encompass the column that you are trying to return data from, so in your example you will need:

=VLOOKUP(D8,A15:C240,3)

The 3 denotes the data is to come from the third column in the lookup range. Only values in the first column (ie col A) are used to compare to the value in D8.

#### NextWorldAngel

##### New Member

Okay the formula worked but...I'm also using an auto filter on all of my columns and every time I filter it gives me of the way results. It works if my first column is sorted by accending, but every other filter causes the formula to pull the wrong name out of column C.

Is there any way to fix this?

#### Richard Schollar

##### MrExcel MVP
There's an optional 4th argument to Vlookup - at present you have it set to True (as that's the default) which means it requires the lookup range to be sorted. You can set it to falase which effectively means the vlookup will only return a non-error value if it finds an exact match in the lookup range (and this might be perfectly OK for you depending on your needs):

Code:
``=VLOOKUP(D8,A15:C240,3,False)``

Come back if this still doesn't do what you want.

#### NextWorldAngel

##### New Member
Perfect, thank you so much, and thank you for explaining what everthing does, it really helps people get the concept for themselves.

Replies
1
Views
22
Replies
1
Views
17
Replies
2
Views
149
Replies
2
Views
92
Replies
3
Views
70