Lookup Formula

ClaireK

New Member
Joined
May 9, 2006
Messages
2
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.
 

Some videos you may like

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
Joined
Apr 19, 2005
Messages
23,707
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.

Does this help you?
 

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 19, 2006
Messages
31

ADVERTISEMENT

=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
Joined
Apr 19, 2005
Messages
23,707
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.

Hope this sorts your problem!
 

NextWorldAngel

New Member
Joined
Oct 19, 2006
Messages
31

ADVERTISEMENT

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
Joined
Apr 19, 2005
Messages
23,707
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
Joined
Oct 19, 2006
Messages
31
Perfect, thank you so much, and thank you for explaining what everthing does, it really helps people get the concept for themselves.
 

Watch MrExcel Video

Forum statistics

Threads
1,108,993
Messages
5,526,114
Members
409,685
Latest member
Bellybb

This Week's Hot Topics

Top