Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

What is wrong with this VLOOKUP formula in Excel 2007?

Posted on 2016-09-29
13
48 Views
Last Modified: 2016-09-30
I can't find anything wrong with this formula:

=IFERROR(VLOOKUP(C1;Travbanor!A6:C100;3;FALSE);"")

But it doesn't return the value from column 3 on the tab "Travbanor".
0
Comment
Question by:hermesalpha
  • 4
  • 4
  • 3
  • +2
13 Comments
 
LVL 7

Expert Comment

by:DPatel
ID: 41822886
=IFERROR(VLOOKUP(C1,Travbanor!A6:C100,3,FALSE),"")

The issue is in parameter seperator.
0
 

Author Comment

by:hermesalpha
ID: 41822887
I have to use semicolon in my operative system.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 41822893
I tried its working for me - below one

=IFERROR(VLOOKUP(C1,Travbanor!A6:C100,3,FALSE),0)
see attached excel

Atatch your excel file if still you face issue
New-Microsoft-Excel-Worksheet.xlsx
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 7

Expert Comment

by:DPatel
ID: 41822894
Yes its working.

It shows "3" as an output of the formula in A Column.
0
 
LVL 30

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41822895
In the VlookUp formula, your lookup value is C1.
Are you sure that your lookup value is found in column A of Travbanor Sheet?

Remember that in VlookUp function, the lookup value should always be in the first column of the table array.
0
 
LVL 7

Expert Comment

by:DPatel
ID: 41822896
Then you will have to change list separator to ";" instead.

To do so, open Region and Settings
Go to Change Date, Time and Number Formats
Click on Advance Settings and there you will find List Separator Field.
Change it to ";"

Then Try!!!
Inform whether its working or not.
0
 
LVL 7

Expert Comment

by:DPatel
ID: 41822898
Hi Neeraj,

I found the lookup value in cell C1.

check.PNG
0
 

Author Comment

by:hermesalpha
ID: 41822902
Subodh, you were right, I didn't have the lookup value in the first column of the table. After I changed according to that it worked fine.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41822905
Glad your issue is resolved.
0
 

Author Comment

by:hermesalpha
ID: 41822907
Just a sidenote: Now I want to have a return value from a column to the left of the lookup column. I guess this isn't possible with VLOOKUP, but what function can I use for that then?
0
 

Author Comment

by:hermesalpha
ID: 41822908
I just found the answer here:

http://www.excel-university.com/how-to-return-a-value-left-of-vlookups-lookup-column/

It seems I should use Match function for looking up a value in the column to the left.
0
 
LVL 30

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41822911
In that case try Index/Match.

Lets say you want to return the value from col. B on Travbanor Sheet where C1 is found in col. C on Travbanor Sheet, your formula should be like this....
=IFERROR(INDEX(Travbanor!$B$6:$B$100,MATCH(C1,Travbanor!$C$6:$C$100,0)),"")

Open in new window

0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41823313
Or you can use just LOOKUP function:

=LOOKUP(LookupValue,LookupRange,ReturnRange)

ReturnRange can be to left or right of LookupRange.

It can also be used for rows and ReturnRange can be above LookupRange.

Also, the two ranges, one can be a row and the other can be a column so long as both ranges are the same size.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

792 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question