Microsoft Excel Question

[Professor1215]

Let's say Albert Pujols has an average vs RHP of .287 and an average vs LHP of .321.

In sheet 1 I have:
In column A titled "Name" I have "Albert Pujols"
In column B titled "v RHP" I have .287.
In column C titled "v LHP" I have .321.

In sheet 2, I want:

To be able to type the name "Albert Pujols" and "RHP" into another column and it give me ".287".

Does this make sense, and if so, how do you do it?

Any help would be greatly appreciated!

[sapidoc]

http://office.microsoft.com/en-us/excel-help/vlookup-HP005209335.aspx

[MonkeyF0cker]

=IF(B1="RHP",VLOOKUP(A1,Sheet1!A1:Sheet1!C30,2,FALSE), IF(B1 = "LHP", VLOOKUP(A1,Sheet1!A1:Sheet1!C30,3,FALSE), ""))

This assumes the range of A1:C30 on Sheet1.

[Professor1215]

=IF(B1="RHP",VLOOKUP(A1,Sheet1!A1:Sheet1!C30,2,FALSE), IF(B1 = "LHP", VLOOKUP(A1,Sheet1!A1:Sheet1!C30,3,FALSE), ""))

This assumes the range of A1:C30 on Sheet1.

I copied that formula and it gives me "FALSE". I made up some random values from A1:C30 on Sheet 1 and I pasted this formula in sheet 2. Not to sound ignorant but I am not sure what I am doing wrong.

I am looking to type the name, Albert Pujols on Sheet 2 on Column A and RHP on Column B and it give me .287 like I have it set up on Sheet 1

VLOOKUP is obviously what I need to use, I must just be too ignorant to figure it out.

Any more suggestions?

[MonkeyF0cker]

It gives you "False?" It shouldn't come up with "False" at all.

Are you sure you copied the formula correctly?

It works for me...

[MonkeyF0cker]

On Sheet2, make A1 the cell where you place the player name. Make B1 the cell where you designate LHP or RHP. And paste the formula into C1.

Then try it.

[That Foreign Guy]

Try naming the data range the batting avg data is in? Maybe your sheets aren't called exactly the same as Monkey's?

[Professor1215]

I got it to work, thank you!

I am now trying to figure out how this formula works for other players with other values.

I thought it would be as easy as dragging down, but I get a "N/A" when I do that.

[thom321]

In order for the formula to work when you drag it down, you have to make the lookup range reference absolute, meaning Sheet1!A1:Sheet1!C30 should be changed to Sheet1!$A$1:Sheet1!$C$30. See the link below for more detail

http://spreadsheets.about.com/od/r/g/absolute_cell_r.htm

Also, I highly suggest using a in-cell drop down list, where you select a name from a list of available names, rather than having to type it. Link below has some detailed instructions on how to do it.

http://www.contextures.com/xldataval02.html

[Professor1215]

Thank you very, very much!!!