|
Some of you know more than many help desks. So I thought I'd ask here.
Is there an easier way to...
I have a large spreadsheet -- One of the fields is a person's name. I'd like to associate one "branch" for about 20 of the names. Another branch for another 10 different names. And so on.
I've started doing -- If this person then this branch, if this person then this branch, etc. It's a bit tedious. Is there a way to use "OR" with this? Something else I'm missing?
Any help would be appreciated.
IF(OR(A1=X,A2=X,B1=X,B2=X),whatever-you-are-doing-here, "")
Or something, anyway you can use a single if structure, but you have to put all the comparisons in the Or structure anyway.
Or you could put them in a named range and then lookup the name in the named range, but I still have no idea what you are doing.
|
|
IF(OR(A1=X,A2=X,B1=X,B2=X),whatever-you-are-doing-here, "")
Or something, anyway you can use a single if structure, but you have to put all the comparisons in the Or structure anyway.Or you could put them in a named range and then lookup the name in the named range, but I still have no idea what you are doing.
Hmmm. Didn't know that I could use "OR" like that. That helps.
Then I saw what you said about "Named Ranges". That might be a better way to go.
I think I know how I would name a range -- How would I say -- If (Cell is in rangeX, then whatever I'm trying to do?
EDIT -- Basically, how do I check if a cell is in a range?
IF(OR(A1=X,A2=X,B1=X,B2=X),whatever-you-are-doing-here, "")
Or something, anyway you can use a single if structure, but you have to put all the comparisons in the Or structure anyway.Or you could put them in a named range and then lookup the name in the named range, but I still have no idea what you are doing.
Hmmm. Didn't know that I could use "OR" like that. That helps.
Then I saw what you said about "Named Ranges". That might be a better way to go.
I think I know how I would name a range -- How would I say -- If (Cell is in rangeX, then whatever I'm trying to do?
EDIT -- Basically, how do I check if a cell is in a range?
IF (ISNA(VLOOKUP(A1,NamedRange,1,FALSE)),"",whatever-you-want-to-do)
Something like that.
Start with a table with two columns. Name the entire range (including both columns). In recent versions, select the entire range, right-click and choose 'Name a Range...' (no real surprise there)
.
.
.
.
.
.
Name 1 | (Group 1 Value)
Name 2 | (Group 1 Value)
Name 3 | (Group 1 Value)
...
Name 21 | (Group 2 Value)
Name 22 | (Group 2 Value)
Name 23 | (Group 2 Value)
...
etc.
In this example, I've named the range "Name_Lookup"
Presume cell A1 has your name in it, and cell B1 has your result.
Cell B1 should have the following formula:
=IF(ISNA(VLOOKUP(A1,Name_Lookup,2,0)),(Default Value),VLOOKUP(A1,Name_Lookup,2,0))
If the value in A1 isn't in your table, you'll get whatever is in the (Default Value) in the formula
If the value in A1 is in your table, you'll get whatever is in the second column. This can be text, numbers, whatever.
If you want to add more values later, simply expand the range, and add new entries to the table.
Hope this helps.