Formula

[Shilps]

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

 

[Bernard Liengme]

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

 

[Bernie Deitrick]

Shilps,

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

HTH,
Bernie
MS Excel MVP

 

[Shilps]

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

Anyone else have a suggestion?

 

[Bernie Deitrick]

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