VLOOKUP(B2,emp2,False) NO RESULT 1

[ZoomerZ]

Hi!
I have 2 spreadsheets.
I need to enter LOOKUP formula on Spreadsheet1 to get value from Spreadsheet2
So far it is VLOOKUP(B2,emp2,False)
emp2 is the name of the Spreadsheet2

Is there error in formula?
It stays as formula - no result
Thanks

 

[DeeBeeGee]

ZZ

the formula should be set out as
=VLOOKUP(value to be found, range to be searched, column of range to return value from, whether an exact match is required)

You need to be more specific with specifying your range.
Something like [emp2]Sheet1!A1:B12 for example.

Then you need to specify which column of the range you want to be returned

=VLOOKUP(B2,[emp2]Sheet1!A1:B12, 2, False )

will return the value in column B in the first row that your specified value is found in Column A

Hope this helps

DBG

 

[ZoomerZ]

Thanks but it does not work...
Guy came yesterday and entered VLOOKUP(B2,emp2,False)
and it worked.
Now I am opening his file in search for the formula.
Should this formula being saved with Excel file?
When I click on the cell - I can see value but not formula.
Thanks

 

[xlbo]

If you saved the excel file - then yes. If not, then No. He could've pasted values over the formula but something in your 1st post makes me think that you have the cell formatted as text which is why it doesn't convert to formula

Format your cell as general and re-enter the formula

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ZoomerZ]

xlbo,
checked-General, thanks I was sure you were right on this one as it is too weird.

Sheet2
A B
emp no Count
100230 3
100440 1
101032 2
101565 2

Sheet1
emp no Occurrences
100230 =VLOOKUP(B2,emp no,False)
101032 =VLOOKUP( )
101565 =VLOOKUP( )

emp no must link

So I am basically want to show in Sheet1!Ocurrences values of sheet2!Count where emp no are equal

 

[xlbo]

ok
You can only use emp no in the formula if it refers to a NAMED RANGE. As you cannot have spaces in a named range, I must conclude that you do not. This being the case, it would be easiest to create a named range. Simply select all the data you want to lookup IN on Sheet2 and follow menu path
Insert>Name>Define
Give the range a name (eg emp_no) and click ok

Once you have that, then the following should work:

=VLOOKUP(B2,emp_no,2,false)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[ZoomerZ]

I know it should work and it would but...
When I went to Sort by - I've got message that some of the fields aren't numbers.
I went and changed emp_no on both WSheets to a numbers.
No luck...

 

[KenWright]

Sounds like you have a mix of text and numbers. You cannot simply format them as numbers and have them change, regardless of what the format option tells you.

You need to select them all, format as numbers or general and then put a 1 in a cell, copy the cell, select all your "numbers" and do Edit / Paste Special / Multiply. This should coerce the data back to numeric.

To do it to an individual cell you have to format as number and then hit F2 and Enter, or double click the cell and hit enter.

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission

----------------------------------------------------------------------------