• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 75
  • Last Modified:

What is wrong with this VLOOKUP formula in Excel 2007?

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
hermesalpha
Asked:
hermesalpha
  • 4
  • 4
  • 3
  • +2
1 Solution
 
D PatelD Patel, Software EngineerCommented:
=IFERROR(VLOOKUP(C1,Travbanor!A6:C100,3,FALSE),"")

The issue is in parameter seperator.
0
 
hermesalphaAuthor Commented:
I have to use semicolon in my operative system.
0
 
Pratima PharandeCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
D PatelD Patel, Software EngineerCommented:
Yes its working.

It shows "3" as an output of the formula in A Column.
0
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
D PatelD Patel, Software EngineerCommented:
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
 
D PatelD Patel, Software EngineerCommented:
Hi Neeraj,

I found the lookup value in cell C1.

check.PNG
0
 
hermesalphaAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Glad your issue is resolved.
0
 
hermesalphaAuthor Commented:
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
 
hermesalphaAuthor Commented:
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
 
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
 
Rob HensonFinance AnalystCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now