Need help with VLOOKUP and Nested IF Functions

Copper Contributor

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. 

Capture.PNG

3 Replies

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

INDEXcolumns.JPG

and in attached file

 

Emily,

 

another lookup variation:

=LOOKUP(9^99,INDEX($H$3:$I$4,MATCH(A3,$G$3:$G$4,0),0))
Thank you both! These have helped a lot!