Formula
 

Hi,

I have an excel sheet where column A is numbers and B is text.

Is there a formula I can use to search column B for a specific text
and then return the the number in column A?

Ie, I want to search in all of column B whether any of the cells says
'yes' for example and return the A cell besides it. So if cell B2 is
'yes' then I want to return cell A2.

Is there a way of doing this?

Many thanks.

Shilps

Formula
 

In A1:A5 I have numbers 5,9,12,15,18
In B1:B5 I have names of animals: monkey, dog, cat , cow, horse
In D1 I typed the word cow
In E1 I have =INDEX(A1:A5,MATCH(D1,B1:B5,0))
It returns the value 15
Best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"Shilps" wrote in message
...
Hi,

I have an excel sheet where column A is numbers and B is text.

Is there a formula I can use to search column B for a specific text
and then return the the number in column A?

Ie, I want to search in all of column B whether any of the cells says
'yes' for example and return the A cell besides it. So if cell B2 is
'yes' then I want to return cell A2.

Is there a way of doing this?

Many thanks.

Shilps

Formula
 

Shilps,

=INDEX(A:A,MATCH("Yes",B:B,FALSE))

HTH,
Bernie
MS Excel MVP


"Shilps" wrote in message
...
Hi,

I have an excel sheet where column A is numbers and B is text.

Is there a formula I can use to search column B for a specific text
and then return the the number in column A?

Ie, I want to search in all of column B whether any of the cells says
'yes' for example and return the A cell besides it. So if cell B2 is
'yes' then I want to return cell A2.

Is there a way of doing this?

Many thanks.

Shilps

Formula
 

Thanks for trying to help me out Bernie, but it doesn't seem to work.
I get N/A.

Anyone else have a suggestion?

On 28 Apr, 14:37, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Shilps,

=INDEX(A:A,MATCH("Yes",B:B,FALSE))

HTH,
Bernie
MS Excel MVP

"Shilps" wrote in message

...

Hi,

I have an excel sheet where column A is numbers and B is text.

Is there a formula I can use to search column B for a specific text
and then return the the number in column A?

Ie, I want to search in all of column B whether any of the cells says
'yes' for example and return the A cell besides it. So if cell B2 is
'yes' then I want to return cell A2.

Is there a way of doing this?

Many thanks.

Shilps

Formula
 

As a test, try

=INDEX(A:A,MATCH(XXXX,B:B,FALSE))

replacing the XXXX with the address of any filled-in cell in column B. Then the formula should
return the value from the same row in column A (If the value in B is unique, that is).

It is possible that your "yes" value is actually " yes" (with a leading space) or "yes " (with a
trailing space)....

HTH,
Bernie
MS Excel MVP


"Shilps" wrote in message
...
Thanks for trying to help me out Bernie, but it doesn't seem to work.
I get N/A.

Anyone else have a suggestion?

On 28 Apr, 14:37, "Bernie Deitrick" <deitbe @ consumer dot org wrote:
Shilps,

=INDEX(A:A,MATCH("Yes",B:B,FALSE))

HTH,
Bernie
MS Excel MVP

"Shilps" wrote in message

...

Hi,

I have an excel sheet where column A is numbers and B is text.

Is there a formula I can use to search column B for a specific text
and then return the the number in column A?

Ie, I want to search in all of column B whether any of the cells says
'yes' for example and return the A cell besides it. So if cell B2 is
'yes' then I want to return cell A2.

Is there a way of doing this?

Many thanks.

Shilps