Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Ben Ben is offline
external usenet poster
 
Posts: 5
Default Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,856
Default Compare Value in Cell 1 to a List, Return Value if Match otherwise Return Null

You're welcome, Ben.

Pete

Thank you very much...huge help!

Ben


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
how to compare cell content to a row pf data in excel and return . Abdel Excel Discussion (Misc queries) 0 December 12th 06 09:43 PM
Compare two cells and return certain value in third cell hurairah Excel Worksheet Functions 3 July 19th 06 03:44 AM
Have DGET return Null instead of #Value! matthoffman33 Excel Worksheet Functions 3 May 6th 06 02:21 AM
Cell to return null instead of 0 dford Excel Discussion (Misc queries) 7 November 28th 05 06:37 PM
return a " " for null values David# Excel Worksheet Functions 2 January 21st 05 06:47 PM


All times are GMT +1. The time now is 09:25 AM.

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

About Us

"It's about Microsoft Excel"