Lookup Formula

[ClaireK]

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.

 

[Richard Schollar]

Hi Claire

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

=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?

 

[NextWorldAngel]

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

 

[Richard Schollar]

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]

=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]

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]

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]

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):

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

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

 

[NextWorldAngel]

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