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

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

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

=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

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".

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

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:

This formula will return TRUE if the value in B2 is found in the list, and FALSE otherwise.