Forum Discussion

Beth Stirling's avatar
Beth Stirling
Copper Contributor
Nov 22, 2017

Formula Help for Vlookup and IF

Hello,

 

Im in need of help.

 

I have a worksheet with three tabs.  Tab A is my working tab.  I need to perform a VLOOKUP/IF Formula to find a work task 'Maths' for example in a Column B, in 'Tab C' and pull the who row of data to Tab A

 

I also then need to find a reference number '1' for example from 'Tab D' and pull the information from Column J only, on this row to  Column J , on Tab C. 

 

 

Can anyone help with these formulas, I'm really struggling with this. 

 

Thanks 

Formulas & Functions
tips and tricks

2 Replies

  • Wyn Hopkins's avatar
    Wyn Hopkins
    MVP
    Nov 23, 2017

    Hi Beth

     

    Unfortunately there is no single formula to bring in a whole row of data from another sheet

     

     

    In cell A1 of Tab A I would write "Maths",

     

    Then in Tab A cell B1:   I would use a =MATCH( $A1,'Tab C'!B:B,0)  to give me the correct row number from Tab C

     

    Then in Tab A cell C1:   I would use  =INDEX('Tab C'!A:A,$B1,0)  and then copy this formula across to bring back the results from each column in Tab C

     

     

    For the 2nd part in Column J on Tab C 

     

    =INDEX('Tab D'!J:J,  MATCH(1,  'Tab D'!A:A, 0 ))

     

    This assumes that the number 1 you are looking up is in column A of Tab D

     

    Hope that guides you in the right direction

     

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Nov 23, 2017

    Beth,

     

    what is the problem? It's a simple lookup.

    =LOOKUP("Maths",'C'!$B:$Z,COLUMNS(B1),0)