Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can we use multiple if with VLookup function | Excel Worksheet Functions | |||
vlookup Function Help Needed | Excel Worksheet Functions | |||
Pastable function using VLOOKUP? | Excel Worksheet Functions | |||
format cell based on results of vlookup function | Excel Worksheet Functions | |||
Vlookup w/Date Function | Excel Worksheet Functions |