IF Statement for Range of options

[BrianP1]

Trying to find if this is possible. I have a list of names which I am trying to do If Statements on. I need the if statement to return a value if a specific cell equals one of the names on the list. The list contains 75 people so would need multiple if statements. Instead of doing an if statement for each name on the list, is there a way for the if statement to look at the entire list at once (using a range or something)?

 

[dreid1011]

Yes, that is possible with an IF and VLOOKUP/LOOKUP combination.

 

[Eric W]

Welcome to the board.

Sounds like you just need a VLOOKUP. Build a table with the names and values you want, then use the VLOOKUP to get the corresponding value:

	A	B	C	D	E
1	Name	Score		Name	Score
2	Erica	99		Ann	80
3				Bob	90
4				Cathy	55
5				Dan	45
6				Erica	99
7				Frank	75

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =VLOOKUP(A2,D2:E7,2,0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[BrianP1]

Yes, that is possible with an IF and VLOOKUP/LOOKUP combination.

Awesome! Can you show me an example?

 

[dreid1011]

If you explain a little bit more about how your data is set up and what value you want to return, then yes. Though, you might take a look at what Eric has posted first and see if that is what you need.

 

[BrianP1]

Welcome to the board.

Sounds like you just need a VLOOKUP. Build a table with the names and values you want, then use the VLOOKUP to get the corresponding value:

	A	B	C	D	E
1	Name	Score		Name	Score
2	Erica	99		Ann	80
3				Bob	90
4				Cathy	55
5				Dan	45
6				Erica	99
7				Frank	75

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =VLOOKUP(A2,D2:E7,2,0) <tbody> </tbody>

<tbody>
</tbody>

Thanks Eric. Unfortunately it is a little more complicated than that. I have a table of 50,000 rows of data that I need to check to see if a value in a collumn for each of the rows contains a sales rep name in a separate table of 75 names.

 

[Eric W]

Now it sounds more like you want a MATCH function. Try:

	A	B	C	D
1	Name	Found rep?		Name
2	Erica	TRUE		Ann
3	Ann	TRUE		Bob
4	Bob	TRUE		Cathy
5	Erica	TRUE		Dan
6	Dan	TRUE		Erica
7	Mark	FALSE		Frank
8	Ed	FALSE
9

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B2 =ISNUMBER(MATCH(A2,$D$2:$D$7,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

If this isn't what you want, then please show a sample of your sheet, and the expected results. You can use the HTML Maker in my signature to make sample sheets like I did, or just use the table tool on the Advanced menu.

 

[BrianP1]

If you explain a little bit more about how your data is set up and what value you want to return, then yes. Though, you might take a look at what Eric has posted first and see if that is what you need.

Here is an example.

Need to see if Column C in Table 1 contains a name that is in the Table 2 Column A.

Table 1
A B C D E
Company X Deal 1234 Joe Smith $142.01 Product X
Company D. Deal 3364. Bob Hope $443.76 Product P
Company Q. Deal 2575. Rick Burns. $236.56. Product A


Table 2
A
Richard Watson
Bob Hope
Jane Doe
Joe Smith
Eric Poster
Trish Nixon
Erica Andrews
Liam Yon

 

[BrianP1]

A	B	C	D	E
Company x	Deal 1234	Joe Smith	$142	Product X
Company D	Deal 3364	Bob Hope	$443	Product P
Company Q	Deal 2575	Rick Burns	$236	Product A

<tbody>
</tbody>

Table 2

A	B
Richard Watson	Thomas Preston
Bob Hope	Thomas Preston
Jane Doe	Thomas Preston
Joe Smith	Samuel Rogers
Eric Poster	Samuel Rogers
Trish Nixon	Walter Lin
Erica Andrews	Walter Lin

<tbody>
</tbody>

The code I need will be within the 1st table, looking at Column C to see if the name matches a value in Table 2 Column A. If it matches, it would return the value in Table 2 Column B. If there is no match, then I would like it to say "No".

Hope this is clearer.

 

[Eric W]

OK, back to the VLOOKUP:

	A	B	C	D	E	F	G	H	I	J
1	Company	Deal	Rep	Amount	Product	Acct. Exec		Rep	Acct. Exec
2	Company x	Deal 1234	Joe Smith	$142	Product X	Samuel Rogers		Richard Watson	Thomas Preston
3	Company D	Deal 3364	Bob Hope	$443	Product P	Thomas Preston		Bob Hope	Thomas Preston
4	Company Q	Deal 2575	Rick Burns	$236	Product A	No		Jane Doe	Thomas Preston
5								Joe Smith	Samuel Rogers
6								Eric Poster	Samuel Rogers
7								Trish Nixon	Walter Lin
8								Erica Andrews	Walter Lin
9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet3​

Worksheet Formulas Cell Formula F2 =IFERROR(VLOOKUP(C2,$H$2:$I$8,2,0),"No") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>