Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 14
Default Check to see if a value exists in a list?

Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2 exists
in A1:A3, or false otherwise?

Thanks
Pradhan
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Check to see if a value exists in a list?

Hi Pradhan,

Yes, there is a function in Excel that can help you check if a particular value exists in a list. It's called the "MATCH" function. Here's how you can use it:
  1. Select the cell where you want to display the result (in this case, B2).
  2. Type the following formula:
    Code:
    =MATCH(B2,A1:A3,0)
  3. Press Enter.

The MATCH function will search for the value in B2 (i.e. "car") in the range A1:A3. The "0" at the end of the formula tells Excel to look for an exact match. If the value is found in the list, the function will return the position of the value in the list (i.e. 1 for "car", 2 for "train", etc.). If the value is not found, the function will return the #N/A error.

To convert the result to TRUE or FALSE, you can use the ISNUMBER function. Here's the updated formula:

Code:
=ISNUMBER(MATCH(B2,A1:A3,0))
This formula will return TRUE if the value in B2 is found in the list, and FALSE otherwise.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 15,768
Default Check to see if a value exists in a list?

One way...

=COUNTIF(A1:A3,B2)0

--
Biff
Microsoft Excel MVP


"Pradhan" wrote in message
...
Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2
exists
in A1:A3, or false otherwise?

Thanks
Pradhan



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,520
Default Check to see if a value exists in a list?

Try
=IF(COUNTIF(A1:A3,B1),TRUE,FALSE)

--
Jacob


"Pradhan" wrote:

Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2 exists
in A1:A3, or false otherwise?

Thanks
Pradhan

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,718
Default Check to see if a value exists in a list?

=OR(INDEX(A1:A3=B2,))


"Pradhan" wrote:

Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2 exists
in A1:A3, or false otherwise?

Thanks
Pradhan



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,766
Default Check to see if a value exists in a list?

Hi,.

Array enter the following formula (Ctrl+Shift+Enter)

=B2=A1:A3

--
Regards,

Ashish Mathur
Microsoft Excel MVP

"Pradhan" wrote in message
...
Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2
exists
in A1:A3, or false otherwise?

Thanks
Pradhan


  #7   Report Post  
Junior Member
 
Posts: 1
Smile

I like this option better for cells that contain text. Because I tried to use a countif when looking for a cell that contain the text "2419.6", but when typing in the formula =COUNTIF(range,"2419.6") it would count only values that are greater than 2419.6 rather than the entered text of "2419.6".

Quote:
Originally Posted by Teethless mama View Post
=OR(INDEX(A1:A3=B2,))


"Pradhan" wrote:

Hi and thanks again for your help out there...
Is there any function to test if a particular value exists in a list?
A simple example: Column A1= car; A2=train; A3=plane

In B2 I place "car". Is there a function which will return TRUE if B2 exists
in A1:A3, or false otherwise?

Thanks
Pradhan
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 check to see if a sheet with a particular name exists? Varun Excel Worksheet Functions 3 January 25th 09 02:41 PM
check if sheet exists mohavv Excel Discussion (Misc queries) 1 November 21st 07 02:58 AM
check if the sheet/tag exists Alex Excel Worksheet Functions 2 March 14th 06 09:58 PM
check if worksheet exists joeeng Excel Worksheet Functions 3 September 7th 05 06:49 PM


All times are GMT +1. The time now is 12:45 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"