I can enter an array formula that puts output into each cell in a column, and I can enter an array formula that puts output into a single cell, but I’m hoping there’s a way to write an array formula that puts output into a group of cells without putting output into every cell in a column.
So for example this formula puts the word “greater” or the words “not greater” into every cell in the column the formula was entered into: {=IF(L:L>M20,“greater”,“not greater”)}
But can I make it do something like that–but only have it apply to cells in rows 8, 9 and 10?
Sorry nevermind, I just figured it out
What about this, then.
I want a formula to check whether the value in one cell is equal to the value in any of a number of other cells, and I want to do it without just nesting a whole bunch of “if” formulas. Is this possible?
The MATCH function searches for a value within a range and returns its position. Use that with ISERROR to determine if the value was found.
=IF(ISERROR(MATCH(B1,A1:A10,0)),"Not found","Found")
I’m getting a weird result. Here’s the formula:
=IF(MATCH(K2,A14:A21),“found”)
Shouldn’t that see whether the value in K2 matches any of the values from A14 through A21, and display “found” only if it finds such a match?
In case it matters, the values are alphanumeric, not just numbers.
The weird result I’m getting is that it sometimes (but not always) says “found” even when there’s not a match as I would judge it. The list just contains written numbers one, two, three, four and so on. The value in K2 is “zebra” right now. Yet the formula returns “found”, as though “zebra” matched one of the items on that list. It doesn’t.
A few issues: first, you omitted the Match Type parameter so it defaults to 1, which means it finds the value greater than or equal to what you provided. You need to specify Match Type of 0 for exact matches.
Second, if it doesn’t find a match then it returns an error, so you need to use ISERROR to check for this. The formula I pasted above should work.
I don’t mind it returning an error on no match, but just to make sure I could make it work some way, I tried using exactly your formula (with cell numbers adjusted). Now for whatever reason, it returns “no match” no matter whether there is a match or not.
Formula is: =IF(ISERROR(MATCH(K2,A14:A21,0)),“Not found”,“Found”)
After typing the above paragraph and then fiddling around some, I think I have discovered what the problem is, but I don’t know how to solve it. It appears that it matters whether or not what is in K2 is text that I typed, or instead, a formula which outputs text. I didn’t think this distinction would be visible to the formula above, but apparently it is. In excel, is there a way to say “use the value of the formula, not the formula itself”?
I’m not seeing that in my spreadsheet. I tried putting a formula in K2 that concatenates other cells, and it still matches for me. What kind of formula do you have? What version of Excel? I’m on 2010.
Okay, here’s what I have entered into cells:
In H2 – two potato
In K2 – =LEFT(H2, FIND(" ",H2))
In A14 – one
In A15 – two
.
.
In A21 – eight
And finally
In E16 – =IF(ISERROR(MATCH(K2,A15:A22,0)),“Not found”,“Found”)
As for what shows as output in the cells with formulas:
K2 – two
E16 – Not found
I’m using Excel 2010
BTW I tried replacing K2 in E16’s formula with just the formula found at K2, so that it read:
=IF(ISERROR(MATCH(LEFT(H2, FIND(" ",H2)),A14:A21,0)),“Not found”,“Found”)
But this seems to give identical results. (Which is to say–so far it says “Not found” no matter what I put into H2.)
Ah… maybe it’s including the space in K2
ETA: Yes that was it. Forehead slappy.
Was just about to suggest that. Glad it works!