Excel help - Fantasy Baseball Cafe 2014

## Excel help

Moderator: Baseball Moderators

### Excel help

Hey guys

What I'm trying to do is a little complex.

- I have a range - 3 columns.
- In column A, I have people's names
- Column B has their age
- Column C has their salary

Basically I have another sheet, and I want a particular cell to display the person's salary given two criteria from column A & B.

i.e. IF anywhere in column A (A:A?) = "Jim" AND anywhere in column B (B:B?) = "45", then display "\$45,000" (C? it would be on the same row...) in D21

I'd imagine a problem would arise if you had two rows that satisfied the conditions, but that's not the case here.

I thought I could just use an "IF" statement such as =IF(A:A="Jim" & B:B="45", "\$45,000") however I think it's having issues that I entered a range. Not to mention I need to ask it to display information from the cell in column C, where the conditions from column A & B are satisfied.

Sorry if this is unclear - can answer questions.
RynMan
Hall of Fame Hero

Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

### Re: Excel help

Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:

=IF(A:A="Jim","\$45,000",if(B:B="45","\$45,000","False"))

If that doesn't work for you, a hard workaround is to actually seed data in the subsequent column. So you have an =OR(A1="Jim",B1="45"). You can then key off of the Boolean response in your destination formula with something like a VLOOKUP or whatever's appropriate for your needs. Oh, I always recommend using the actual cell range for a column: A1:A65536, B1:B65536, etc. Some formulas don't like the A:A syntax.

Let me know if that doesn't help you out.

JTWood
Hall of Fame Hero

Posts: 11508
Joined: 22 Jun 2004
Home Cafe: Baseball
Location: Unincorporated Heaven

### Re: Excel help

Use VLOOKUP. I've had to do a similar thing with names and payments, but this was with two columns, and the condition was to list only people who have made over \$20.
The Artful Dodger
Chief Wikitect

Posts: 21939
(Past Year: 675)
Joined: 3 Feb 2006
Home Cafe: Baseball

### Re: Excel help

JTWood wrote:Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:

Sure you can.

If you're trying to fully contain this into one sheet and only display in Column D the results of your search, then you should go row-by-row as opposed to entering a range.

For instance, assuming row 1 holds column headers (name/age/salary), then your formula in D2 would look like this: =IF(AND(A2="Jim",B2=45),C3,"") and the quotes in the last part of the formula just return an empty cell. Then you copy that formula all the way down and only if you're searching for that one person does his salary come up.

Now, if you're trying to search the entire range and just return one entry, then you have to go VLOOKUP. You concatenate Name and Age in a lookup column in A (so insert a column before A). A2 would become =B2&C2. Then you just have an area where you do your lookup and the formula would be =VLOOKUP("Jim"&"45",\$A\$2:\$D\$1000,4,0). That pulls Jim's salary when it finds the right one. Of course the 1000 changes to whatever your range actually is. And I'd probably reference cells for the "Jim" and "45" in the lookup so it's much easier to use and update. Let me know if this isn't explained very well.
Big Pimpin
Mod in Retirement

Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

### Re: Excel help

Big Pimpin wrote:
JTWood wrote:Unfortunately, you can't use multiple conditions (AND,OR) in an IF statement. You'll have to nest them:

Sure you can.

=IF(AND(A2="Jim",B2=45),C3,"")

D'oh! I was thinking of SUMIF.
StlSluggers
Hall of Fame Hero

Posts: 14716
Joined: 24 May 2004
Home Cafe: Baseball
Location: Parking in the gov't bldg @ 7th and Pine. It's only \$3.00 on game day!

### Re: Excel help

Using INDEX and MATCH

Code: Select all
`       A          B          C1    Player      Age      Salary2    Alex        32       275003    Derek       33       189004    Manny       36       20000`

=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".
roninmedia
Major League Manager

Posts: 1647
Joined: 16 Jul 2003
Home Cafe: Baseball

### Re: Excel help

roninmedia wrote:Using INDEX and MATCH

Code: Select all
`       A          B          C1    Player      Age      Salary2    Alex        32       275003    Derek       33       189004    Manny       36       20000`

=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".

Wow - that was way too easy;

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?
RynMan
Hall of Fame Hero

Posts: 8891
Joined: 4 Feb 2004
Home Cafe: Baseball
Location: Oztailia

### Re: Excel help

RynMan wrote:
roninmedia wrote:Using INDEX and MATCH

Code: Select all
`       A          B          C1    Player      Age      Salary2    Alex        32       275003    Derek       33       189004    Manny       36       20000`

=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".

Wow - that was way too easy;

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?

I got this one!

Delete the formula from it

Jacoby Ellsbury - Sponsored by the Fantasy Baseball Cafe!

Play Pick 3!

Like us on Facebook
bigken117
Bouncer

Posts: 12879
(Past Year: 250)
Joined: 15 Feb 2004
Home Cafe: Baseball
Location: posed in an awkward stance

### Re: Excel help

RynMan wrote:
roninmedia wrote:Using INDEX and MATCH

Code: Select all
`       A          B          C1    Player      Age      Salary2    Alex        32       275003    Derek       33       189004    Manny       36       20000`

=INDEX(C2:C4,MATCH(1,IF(A2:A4="Alex",IF(B2:B4=32,1)),0))

Blue: Range in which values, you want to output, in this case, his salary when search results are found
Red: Range in where you're searching for his name, Alex
Green: Range in where you're searching for his age, 33

Note:
Control+Shift+Enter (not just with enter) and copy down.
Try to keep the data in the range to one type, for example, if you include the cell that says "Salary" in the salary column, it will mess up. Text and Numbers don't mix.
Put the name you want to find in " ".

Wow - that was way too easy;

Is there any way to not have "#N/A" in the cell when no value is found? Or is that pushing it?

Can you set up a condition in which if the cell reads #N/A it is replaced with "-" or " "?
bigh0rt
Hall of Fame Hero

Posts: 24816
(Past Year: 357)
Joined: 3 Jun 2005
Home Cafe: Baseball
Location: Crowding The Plate

### Re: Excel help

bigh0rt wrote:Can you set up a condition in which if the cell reads #N/A it is replaced with "-" or " "?

Yep...

=IF(ISNA(Formula),"",Formula)

Big Pimpin
Mod in Retirement

Posts: 13710
(Past Year: 4)
Joined: 20 Apr 2005
Home Cafe: Baseball
Location: Building a metric. And being ignorable and stupid.

Next

### Who is online

Users browsing this forum: No registered users and 3 guests

Forums Articles & Tips Sleepers Rankings Leagues

Today's Games
Wednesday, Sep. 3
(All times are EST, weather icons show forecast for game time)

 • Philadelphia at Atlanta(12:10 pm)
 • Pittsburgh at St. Louis(1:45 pm)
 • San Francisco at Colorado(3:10 pm)
 • Washington at LA Dodgers(3:10 pm)
 • Seattle at Oakland(3:35 pm)
 • Boston at NY Yankees(7:05 pm)
 • Detroit at Cleveland(7:05 pm)
 • Cincinnati at Baltimore(7:05 pm)
 • NY Mets at Miami(7:10 pm)
 • Toronto at Tampa Bay(7:10 pm)
 • Milwaukee at Chi Cubs(8:05 pm)
 • LA Angels at Houston(8:10 pm)
 • Texas at Kansas City(8:10 pm)
 • Chi White Sox at Minnesota(8:10 pm)
 • Arizona at San Diego(10:05 pm)