Forum Discussion
Solved
Baseball Spreadsheet Help
Hello to anyone who can help. I currently face a problem with my excel worksheet. I am trying to figure this out but to no avail so far. I'll explain the problem and then attach an image. ***IF Corey Seager IS FACING a LEFTY, then RETURN one value, OTHERWISE RETURN other value***
The fact of the matter is I need this done for each hitter. I realize once I get the first function in there it will be easy but I cannot figure this out. The hitters can either be facing a "righty" or a "lefty." Depending on which one they face, a certain value that I have located on another page will be returned.
Any help would be great.
Thank you
Hi John,
if your lookup data is in sheet named 'Lookup', and you have names in the range A2:A30, Lefty values in the range B2:B30, and Righty values in the range C2:C30, then your formula in the cell K2 of the 'New Hitters' sheet would be
=IF($G2="Left",INDEX(Lookup!$B$2:$B$30,MATCH($A2,Lookup!$A$2:$A$30,0)), INDEX(Lookup!$C$2:$C$30,MATCH($A2,Lookup!$A$2:$A$30,0)))
Yury
5 Replies
Hi John,
If the values to be returned are in a different sheet sitting one below the other (i.e., in two rows) and are based on just LEFT pr RIGHT values in column G then you can do a VLOOKUP. Assuming that you need to have the returned values in column K, place the below formula with necessary changes in cell K2:
VLOOKUP(G2, lookupSheetName!$A$1:$B:$2, 2, FALSE)
Here:
- "lookupSheetName" is the name of the sheet in your workbook from where you want to pick up the desired corresponding values for LEFT/RIGHT.
- You can replace the cell range in the formula - "$A$1:$B:$2" with the range address where you have the lookup data. But make sure you are prefixes $ signs for columns and rows in the range address here.
- the third parameter in the function - "2" - indicates the column number enumerated from left to right in the lookup range that has the value to be returned.
- last parameter - "FALSE" - indicates you want to do exact match of the values in column G2 and the first column in the lookup range.
After this, just copy the formula and paste it down the column until you need it.
Hope that helps..!!
Hi John,
you can use the following formula in cell K2
=IF($G2="Left","ReplaceWithYourLeftyValue","ReplaceWithYourRightyValue")
Once you have put it there, you can copy it down to the other cells in the row.
This assumes that you your Righty and Lefty values are not player specific. In case they are player specific, you would need to use INDEX/MATCH to lookup the values for each player. Please let me know if this the case, and I will send you an updated formula.
If you find my answer useful and like it, I would appreciate you clicking the 'Like' button :)
Regards
Yury
Hello Yury,
I will need to use the INDEX/MATCH function because the values are player specific. If you can update the formula that would be awesome.
Thank you,
John
Hi John,
if your lookup data is in sheet named 'Lookup', and you have names in the range A2:A30, Lefty values in the range B2:B30, and Righty values in the range C2:C30, then your formula in the cell K2 of the 'New Hitters' sheet would be
=IF($G2="Left",INDEX(Lookup!$B$2:$B$30,MATCH($A2,Lookup!$A$2:$A$30,0)), INDEX(Lookup!$C$2:$C$30,MATCH($A2,Lookup!$A$2:$A$30,0)))
Yury
.png){kind=link}