# Can you do this with arrays in Excel? **URL:** \ **Category:** Factual Questions\ **Created:** [May 9, 2014, 7:06pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077 "2014-05-09T19:06:02Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 7:06pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/1 "2014-05-09T19:06:02Z")
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? ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 7:08pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/2 "2014-05-09T19:08:48Z")
Sorry nevermind, I just figured it out ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 7:24pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/3 "2014-05-09T19:24:26Z")
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? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [May 9, 2014, 8:13pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/4 "2014-05-09T20:13:31Z")
> [@Frylock](#): > > 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. ```auto =IF(ISERROR(MATCH(B1,A1:A10,0)),"Not found","Found") ``` ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 8:59pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/5 "2014-05-09T20:59:53Z")
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. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [May 9, 2014, 9:04pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/6 "2014-05-09T21:04:59Z")
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. ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 10:55pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/7 "2014-05-09T22:55:08Z")
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”? ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [May 9, 2014, 11:06pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/8 "2014-05-09T23:06:00Z")
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. ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 11:34pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/9 "2014-05-09T23:34:17Z")
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.) ---
**Author:** ![Frylock](https://avatars.discourse-cdn.com/v4/letter/f/ce7236/32.png) [@Frylock](https://boards.straightdope.com/u/Frylock)\ **Post date:** [May 9, 2014, 11:37pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/10 "2014-05-09T23:37:38Z")
Ah… maybe it’s including the space in K2 ETA: Yes that was it. Forehead slappy. ---
**Author:** ![TroutMan](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/troutman/32/6721_2.png) [@TroutMan](https://boards.straightdope.com/u/TroutMan)\ **Post date:** [May 9, 2014, 11:38pm UTC](https://boards.straightdope.com/t/can-you-do-this-with-arrays-in-excel/688077/11 "2014-05-09T23:38:36Z")
Was just about to suggest that. Glad it works!