Excel help

[RynMan]

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.

[JTWood]

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.

[The Artful Dodger]

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.

[Big Pimpin]

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.

[StlSluggers]

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.

[roninmedia]

Using INDEX and MATCH

Code: Select all

       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    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 " ".

[RynMan]

Using INDEX and MATCH

Code: Select all

       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    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?

[bigken117]

Using INDEX and MATCH

Code: Select all

       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    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

[bigh0rt]

Using INDEX and MATCH

Code: Select all

       A          B          C
1    Player      Age      Salary
2    Alex        32       27500
3    Derek       33       18900
4    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 " "?

[Big Pimpin]

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

Yep...

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