Forum Discussion

Emily A's avatar
Emily A
Copper Contributor
Sep 16, 2017

Need help with VLOOKUP and Nested IF Functions

I Need some help with a VLOOKUP Formula. 

I have a spreadsheet where you dump results from samples. However sometimes Samples are tested twice. 

So I need a formula that will look up the Sample ID from one sheet, and If there is two results for that sample, use Result2 , however if there isnt a second result (appears as --), I need the table to display the Result1.

 

Thanks in advance. 

Formulas & Functions
Need Help

3 Replies

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Sep 17, 2017

    Emily,

     

    another lookup variation:

    =LOOKUP(9^99,INDEX($H$3:$I$4,MATCH(A3,$G$3:$G$4,0),0))
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Sep 16, 2017

    Hi Emili,

     

    For the date located as on your screenshot you may use something like

    =INDEX($G$3:$I$10,MATCH($A3,$G$3:$G$10,0),3-(INDEX($G$3:$I$10,MATCH($A3,$G$3:$G$10,0),3)="--"))

    First MATCH finds the row there the ID is located, and from the column number within the range for the second RESULT we deduct 1 if nested INDEX/MATCH finds "--" here - thus use second column, or RESULT1

    and in attached file

     

Resources