Formula help

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
I am using the formula :
=VLOOKUP(B6,Sheet2!C4:C30,4,FALSE)

I thought this would look in cell B6 then look in sheet2 and enter the info in the relevant cell from column 4, but I keep getting the result #REF.

Someone help....
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
I am using the formula :
=VLOOKUP(B6,Sheet2!C4:C30,4,FALSE)

I thought this would look in cell B6 then look in sheet2 and enter the info in the relevant cell from column 4, but I keep getting the result #REF.

Someone help....

=VLOOKUP(B6,Sheet2!C4:F30,4,FALSE)

you have to change the table array to include the column you want to return data from. So the 4 would be F.
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
It should be C4:F30.

By using 4, you're telling it to look outside of the Lookup range when defined as C:C.

HTH,

Smitty
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Hi

try


=VLOOKUP(B6,Sheet2!C4:F30,4,FALSE)

This will find the value from B6 in sheet2c4:c30, the bring back the value from column F that matches. If there is no exact match for the item, it will return an error.


HTH

Tony
 

Peter1973

Well-known Member
Joined
May 13, 2006
Messages
934
Is there any way I could expand on this formula so that instead of looking in sheet2 it looks into cell A1 then uses the formula to mathc the sheet that matches A1 ?
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
Try

=VLOOKUP(B6,INDIRECT("'"&A1&"'!C4:F30"),4,FALSE)

where A1 contains the relevant sheet name
 

Forum statistics

Threads
1,141,625
Messages
5,707,470
Members
421,510
Latest member
haroonstr

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