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.

I don't see that working for 2 reasons.

1) ">D1" is just taken as that string of 3 chars. Don't need cell D1 at all; just use ">4" that does work.

2) Anyway this just counts up how many cells are not 4, not how many are 4.

It looks for a number in the range matching the number specified in D1.

If you enter a '4' in D1, it will look for the number 4, return error for none, or more than 1, or return the corresponding text in the B column. You could add in another cell for text match TRUE/FALSE, but it gets silly if there's the likelihood of duplication.

Hi Guys,

I wont be back at the computer with the document on until tomorrow so can't try any of the above yet.

Basically I have a table where I am entering data and to the right of that I have a matrix setup and was trying to get the cells in the matrix to return True or False from the data I have entered in to he table. I was then going to set a conditional format so if it returned false the cell would be red and if true the cell would be green.

I will try and do a rough set out below not sure if it will look the same once posted


A B E F
Number Text Header (hello) Header (Goodbye)
1 hello
1 hello
1 goodbye
2 goodbye
2 hello


So basically in column A there could be any number entered up 52.
In columns E1 and F1 I just need it to return true or false if there is a 1 in Column A with the text in Column B that matches the header of the cell in E or F.

This will be carried on in E2 and F2 looking to see if there is a 2 in Column A and the text next to it in Column B I wil do this all the way down to 52 in Column E and F.

Column E and F doesn't need to know how many instances of say 1 and said text there are it just need to know that there is at least one occurence or return false if not.

Hope this helps as I know it's not easy if you don't actually have the document.

Cheers

In reply to a post by camieabz:

It looks for a number in the range matching the number specified in D1.

I don't see how you can imply the criteria "<D1" OR ">D1" match, i.e. equals, the number D1.

They are looking (if they worked *) for the numbers, in the range, that are above or below the value of D1, but not the same as it.

If you really want to find out if there are none or more than 1 value of D1 in the range, all you have to say is:
= COUNTIF(A:A,D1) <> 1

* P.S. On my old Excel these criteria in text "s are just taken as text. It may be in newer versions that the are evaluated as expressions. Nevertheless, my other objection still holds true.

IMO you're splitting hairs on the method, rather than whether the solution is good or not. The formula works.

Another way, which keeps the formulae a little easier, is to insert another column say C and in that column concatinate the value if columns A and B. You can then use a simple countif to see if any of the values in column C are 4hello.

An example here

Sorry, but it makes no logical sense!

It may be relying on some quirk, like 5 iis TRUE and 0 is FALSE.

Do me a favour, in the =IF .. statement replace all the D1s by 4 (or whatever you were searching for) and see if it still works. I hope you agree they are equivalent.

Edited by XRaySpeX (Wed 01-Feb-12 13:34:55)

Hello again.

Im now back at the document.

=AND(COUNTIF($A:$A,1),COUNTIF($B:$B,"TEXT"))
This formula is returning a TRUE for all numbers even if in the formula above it is just asking to return a TRUE for the number 1 with TEXT next to it.


=VLOOKUP(1,$A:$B,2,FALSE)="TEXT"
This formula seems to work until I change the 1 to another number between 1 and 52 then it seems to return TRUE for some cells but not other.
For example =VLOOKUP(4,$A:$B,2,FALSE)="OTHERTEXT" returns a false even though in the data table ther is a 4 in column A with a OTHERTEXT in column B next to it.

It seems both these formulas nearly work so any more help is appreciated.


Thanks

Ok solved at last.

I ended up doing what phillippercival suggested and created another column in my data table and used =CONCATENATE(A2,B2) which gave me the combined data of the 2 columns.

Then in my matrix I used =AND(COUNTIF($C:$C,"1HELLO")) and for cells in the matirx that I wanted to look for a different number I would just change the 1 to a 4 or so on.

All seems to work so thanks to you 3 I can now save some time when I enter data even though I have probably spebt a couple of hours trying to the above.


Thanks

In reply to a post by Anonymous:

Then in my matrix I used =AND(COUNTIF($C:$C,"1HELLO")) and for cells in the matirx that I wanted to look for a different number I would just change the 1 to a 4 or so on.

Hint if ever you have to change anything on a spreadhseet then it should be typed into a box. So if the number you are looking for changes, then put it in a cell at the top somewhere, say I1, then reference I1 in your countif. Actually do the word as well.

That way you do not end up editing the formulae every time you change the number or word.

Example of the result here

edit: apologies if grandmothers and eggs spring to mind

Edited by philippercival (Wed 01-Feb-12 22:21:01)