Home |
Search |
Today's Posts |
|
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
I'm trying to compare a value in a cell to see if it is a match to a
list. Since the list is a single column, I was not certain how to do this and attempted via the VLookup option and put my list into Column A and in Column B...so if there is a better way, I should probably use that. Since I know if no other way, I used VLookup with mixed results. My formula is as follows: =IF(D2="","",(IF(D2<VLOOKUP(D2,Lookup!A:B,2),"",V LOOKUP(D2,Lookup!A:B, 2)))) Here is my problem with results, and it does not make sense to me: Where D2 = "", I receive blank (OK) Where D2 = "Cost Group", there is no match and I receive blank (OK) Where D2 = "Administrative", it returns "Administrative" because it is found in Lookup Table (OK) Where D2 = "283111 - Achievement & Development", there is no match but it returns #N/A (Not OK) Why for "Cost Group" does it return blank but not for "283111 - Achievement & Development" ?? Neither values are in the lookup table. I'm trying to avoid #N/A being returned. Thanks!!! |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You would be better off using MATCH here, but to answer your main
query - there is a fourth parameter which can be used with VLOOKUP to specify that you want an exact match (set it to FALSE or 0). If it is set to TRUE or omitted, then Excel expects the list to be sorted and returns a value based on the highest in the list which is less than the lookup value. Consequently "Cost Group" does not return an error as a match is made to the nearest (alpha) value. However, "283111 - Achievement & Development" begins with a number and presumably all your items in the list are text - therefore there is no lower value than this in your list and an error (#N/A) is returned. Keeping with your formula, you can trap this and simplify with: =IF(ISNA(VLOOKUP(D2,Lookup!A:B,2,0)),"",VLOOKUP(D2 ,Lookup!A:B,2,0)) Hope this helps. Pete On Mar 14, 4:23 pm, "Ben" wrote: I'm trying to compare a value in a cell to see if it is a match to a list. Since the list is a single column, I was not certain how to do this and attempted via the VLookup option and put my list into Column A and in Column B...so if there is a better way, I should probably use that. Since I know if no other way, I used VLookup with mixed results. My formula is as follows: =IF(D2="","",(IF(D2<VLOOKUP(D2,Lookup!A:B,2),"",V LOOKUP(D2,Lookup!A:B, 2)))) Here is my problem with results, and it does not make sense to me: Where D2 = "", I receive blank (OK) Where D2 = "Cost Group", there is no match and I receive blank (OK) Where D2 = "Administrative", it returns "Administrative" because it is found in Lookup Table (OK) Where D2 = "283111 - Achievement & Development", there is no match but it returns #N/A (Not OK) Why for "Cost Group" does it return blank but not for "283111 - Achievement & Development" ?? Neither values are in the lookup table. I'm trying to avoid #N/A being returned. Thanks!!! |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
You're welcome, Ben.
Pete Thank you very much...huge help! Ben |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
how to compare cell content to a row pf data in excel and return . | Excel Discussion (Misc queries) | |||
Compare two cells and return certain value in third cell | Excel Worksheet Functions | |||
Have DGET return Null instead of #Value! | Excel Worksheet Functions | |||
Cell to return null instead of 0 | Excel Discussion (Misc queries) | |||
return a " " for null values | Excel Worksheet Functions |