Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Embedded VLOOKUP function within IF function


Please help!

=IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")

I am trying to lookup two lots of data - one is a filter - "Olivetti
Decommissioning" - then the site name is the second lookup provided the
work is "Olivetti Decommissioning".

The formula above works perfectly well for 95% of my data but for
reasons best known to someone else there are 5 lookup entries that
result in a blank cell (which is stated in the formula if there are no
entries for the site name and "Olivetti Decommissioning"). These 5
entries ARE available on the Engineer planning worksheet. Most of the
entries have more than one entry on the Engineer planning sheet but it
is just the five that are not getting a result. I know that the formula
is referencing properly because I get an #N/A if I change the names
slightly.

I have ensured that the lookup data is spelt the same. I cannot see
what the issue is? Any ideas? Thanks in advance!



Amanda


--
beautyteknorth
------------------------------------------------------------------------
beautyteknorth's Profile: http://www.excelforum.com/member.php...o&userid=37585
View this thread: http://www.excelforum.com/showthread...hreadid=572113

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Embedded VLOOKUP function within IF function

to debug this, I would use the match() function to see what line is being
found in both sections.
=match(F252,'YB Engineer planning'!G:G)
=match(F252,'YB Engineer planning'!A:A)
check over the appropriate number of columns and see what you get.

"beautyteknorth" wrote:


Please help!

=IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")

I am trying to lookup two lots of data - one is a filter - "Olivetti
Decommissioning" - then the site name is the second lookup provided the
work is "Olivetti Decommissioning".

The formula above works perfectly well for 95% of my data but for
reasons best known to someone else there are 5 lookup entries that
result in a blank cell (which is stated in the formula if there are no
entries for the site name and "Olivetti Decommissioning"). These 5
entries ARE available on the Engineer planning worksheet. Most of the
entries have more than one entry on the Engineer planning sheet but it
is just the five that are not getting a result. I know that the formula
is referencing properly because I get an #N/A if I change the names
slightly.

I have ensured that the lookup data is spelt the same. I cannot see
what the issue is? Any ideas? Thanks in advance!



Amanda


--
beautyteknorth
------------------------------------------------------------------------
beautyteknorth's Profile: http://www.excelforum.com/member.php...o&userid=37585
View this thread: http://www.excelforum.com/showthread...hreadid=572113


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Embedded VLOOKUP function within IF function


Hi bj,

I've tried this - there are multiple occurances of the same value in
the list and the list is unsorted so if I omit the match type the
function returns some random value and if I include the match type it
accurately returns the first instance of the value in the list.

I don't get why only 5 values are affected!!

Thanks for your help.

Amanda


--
beautyteknorth
------------------------------------------------------------------------
beautyteknorth's Profile: http://www.excelforum.com/member.php...o&userid=37585
View this thread: http://www.excelforum.com/showthread...hreadid=572113

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav Dav is offline
external usenet poster
 
Posts: 1
Default Embedded VLOOKUP function within IF function


try what bj said but use
=match(F252,'YB Engineer planning'!G:G,0)
=match(F252,'YB Engineer planning'!A:A,0)

to ensure exact matches.

Check for extra spaces after the text strings, they could be causing
you problems

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572113

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
bj bj is offline
external usenet poster
 
Posts: 1,397
Default Embedded VLOOKUP function within IF function

thanks Dav, I knew better (or should have)

"Dav" wrote:


try what bj said but use
=match(F252,'YB Engineer planning'!G:G,0)
=match(F252,'YB Engineer planning'!A:A,0)

to ensure exact matches.

Check for extra spaces after the text strings, they could be causing
you problems

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572113




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Embedded VLOOKUP function within IF function


Hi guys,

Yep, tried all of this. Not solving the problems though. The text
strings are identical and the match function returns (correctly) only
the first entry when using the match type 0.

I'm stumped!




--
beautyteknorth
------------------------------------------------------------------------
beautyteknorth's Profile: http://www.excelforum.com/member.php...o&userid=37585
View this thread: http://www.excelforum.com/showthread...hreadid=572113

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Dav Dav is offline
external usenet poster
 
Posts: 1
Default Embedded VLOOKUP function within IF function


If you are using a match type 0, which is the same as your vlookup in
essence it will always return the first match.

You maybe have to restate your problem giving an example and showing
what you want exactly as the solution. In your example

=IF(VLOOKUP(F252,'YB Engineer planning'!G:H,2,FALSE)="Olivetti
Decommissioning",VLOOKUP(F252,'YB Engineer planning'!A:H,3,FALSE),"")

If the first match of f252 in columnG has olivetti Decommissioning next
to it you are then saying rematch this value in f252 in columnA and
return the value in column c next to it.

If column A is different to column G you will not be returning the same
row

What exactly do you want to achieve?

Regards

Dav


--
Dav
------------------------------------------------------------------------
Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107
View this thread: http://www.excelforum.com/showthread...hreadid=572113

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can we use multiple if with VLookup function Multiple IF's with Vlookup Excel Worksheet Functions 11 August 17th 06 10:28 PM
vlookup Function Help Needed jeffc Excel Worksheet Functions 2 August 14th 06 12:32 PM
Pastable function using VLOOKUP? zatomics Excel Worksheet Functions 1 May 23rd 06 06:17 AM
format cell based on results of vlookup function Edith F Excel Worksheet Functions 1 July 21st 05 07:39 PM
Vlookup w/Date Function cym Excel Worksheet Functions 1 March 25th 05 09:21 PM


All times are GMT +1. The time now is 11:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"