Some Excel Formula help required please

Im not that great with excel formulas so could do with some help if anyone could.

Lets say in column A there could be any number between 1 and 52
In column B there will be some text, for this lets say hello, goodbye, happy ,sad.

The cell with the formula in (lets say E1) I need it to look at the data in column A & B and for this example lets say I want E1 to return true if any of that data in A = 4 and the B cell next to it contains hello.

Any help appreciated.

Use the AND function

E1 would be =AND($A(row number)=4,$B(row number) ="hello")

Use the $ so that if you drag the formula across from E at any point it will still work.

Hope this helps.

Peter

Thanks for the formula but i get an error and it highlights $A as the error.

From the look of the forumla it is just reading from a specified row in column A & B. I need the cell to look at all rows in the columns and return a true if any of the rows in column A with a 4 in and the B cell next to it has a hello. It should return a false if the conditions are not met.

=AND(COUNTIF(A:A,52),COUNTIF(B:B,"hello"))

Edit: Will return "TRUE" if the number 52 is in column A, and the text "hello" is in column B. Will return FALSE if either or both are not present. If you want to make it work for either, change AND to OR.

Edited by camieabz (Tue 31-Jan-12 21:55:18)

Spot on camieabz, worked just as i needed.

Thanks to both of you.

In reply to a post by Anonymous:

and the B cell next to it contains hello.

That last suggestion will return TRUE even if the "hello" is not next to the 4, which is not what you want.

What you need is something like:
=VLOOKUP(4,A2:B11,2,FALSE)="hello"

You may need to get more involved if 4 never appears anywhere, to cope with the resultant "#N/A".

Oops, It would seem I was a bit premature with the celebration.

It works only when there is just one number in column A. As soon as you have different numbers in Column A it returns a TRUE even if I am only looking for it to return a TRUE for "hello" it will still return a true for all the other "TEXT" as well.

Hi XraySpeX,

I have just tried yours and it always returns a FALSE for whatever "TEXT" there is in column B.

Please copy and post your formula here?

Remember that this will only do an exact match on the TEXT. If you have surrounding spaces or different case it will fail unless you get more complicated again.

Ahh. Misread the requirements. Here's one way:

Pick a cell for the numeric entry. I picked D1 in this instance. In cell E1 enter:

=IF(OR(COUNTIF(A:A,"<D1"),COUNTIF(A:A,">D1")),"Error!",VLOOKUP(D1,A:B,2,FALSE))

That line looks for the instances of D1 number in column A, returns error if there's zero or more than one, but if a single instance, returns the contents of the B cell next to it.