Forum Discussion

John Clifford's avatar
John Clifford
Copper Contributor
Aug 01, 2017
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...
Formulas & Functions
Need Help
  • Yury Tokarev's avatar
    Yury Tokarev
    Aug 02, 2017

    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

Deleted's avatar
Deleted
Aug 01, 2017

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..!!

Resources