Okay, this is what I want to do. Say I have some value in A1. I want to see if A1 is the same as any of the values in array B1:B10. It seems simple, but I can’t figure this out. I’ve tried using =IF(A1=B1:B10,1,0), but apparently to do that you have to compare equal sized arrays. I tried doing =OR(A1=B1:B10), but I think I have the same problem. I’d prefer not to use macros since then I’d have to use macros for everything and I’m not too familiar with VBasic.
As long as I’m asking, I’ll also ask this: is there a way to modify a reference within a function? For instance, say I have a formula that contains a reference to A5. Instead of A5, I’d like to make the 5 be a variable so I can increment it when a certain condition is met. I guess I can use the offset function, but that seems a roundabout way of getting what I want.
For your array, I believe you want to use LOOKUP.
I just discovered LOOKUP as well, but I can’t for the life of me get it to work. It seems like I should be able to say:
=LOOKUP(A1,B1:B7)
and presumably it’d give me a TRUE or FALSE as my output (or 1/0, or whatever), but instead it gives me an error. For further clarification, A1 is a number and B1:B7 is a random assortment of numbers (randomly generated).
Okay, nevermind, I found out my error. Apparently if the lookup value is smaller than the first number in the other array, it gives the error. Is there any other function that will return a 1 or 0 depending on if the value is in the array or not?
LOOKUP requires its list argument to be sorted; from your description of these as “random” values it sounds like that may not be the case. Another method would be something like this: Define C1 as =($A$1=$B1) and replicate this for each element in column B. This gives a TRUE result for a match and FALSE otherwise; then just combine these results with =OR(C1:C7). (This gives a TRUE/FALSE result, not 1/0, but that can be changed with IF if needed.)
For your other question, you can use =INDIRECT(ADDRESS(row,column)); there may be a better way.
=IF(ISNA(VLOOKUP(A1,B1:B7,1,FALSE)),1,0)
This does not need the data to be sorted, if all you are interested in is whether A1 is present anywhere in B1:B7.
For your second problem, Omphaloskeptic’s suggestion would work, although INDIRECT(cell ref) would probably be better than INDIRECT(ADDRESS(r,c)) - depends exactly what you’re doing though.
Another method is to name cells in your spreadsheet, and use these names in your formulae. E.g., =A1+somecell. The cell somecell refers to can be changed, although not AFAIK using formulae in cells which may limit its use to you.
Hey, thanks for the responses. I actually figured out a way myself. I used the MATCH(A1,B1:B7,0) for the first part and some OFFSET for the second. Works great! Thanks for the help.