title: Lookup by multiple columns
body: <p><strong>General question:</strong>
  I am trying to look up a name based on a reference table using an index. The values of the first columns in my document and reference table need to match, but I then want to check if the numerical value in my file falls between two numerical columns in my reference table.  If so, I want to print the corresponding name from the reference table into a new column in my file.  Otherwise, I would like to print "NA"</p>
  
  <p><strong>Specific example</strong>
  I want to match the first columns (Chr) of my file and the reference table to see if they match.  If so, I want to see if the value in column 2 of the File (pos) is greater than column 3 of the reference (Start) but less than column 4 of the reference (End). If so, I want to add the corresponding col 4 (Gene_name) from the reference table to a new 4th column of my file.  If there is no corresponding gene, I want to put "NA" in the gene name column of my file.</p>
  
  <p>File</p>
  
  <pre><code>Chr pos     p-val
  2L  1885826 2.638e-08
  2L  1996567 5.12e-05
  2L  2360597 9.472e-05
  2R  2360621 9.472e-05
  2R  2360623 9.472e-05
  </code></pre>
  
  <p>Reference</p>
  
  <pre><code>Chr Start    End        Gene_name
  2L  1884260  1888828    FBgn0262029
  2L  19531851 19547482   FBgn0052532
  2L  2350523  2361570    FBgn0023536
  2L  4647871  4648646    FBgn0029718
  </code></pre>
  
  <p>Desired output</p>
  
  <pre><code>Chr pos     p-val      Gene_name
  2L  1885826 2.638e-08  FBgn0262029
  2L  1996567 5.12e-05   NA
  2L  2360597 9.472e-05  FBgn0023536
  2R  2360621 9.472e-05  NA
  2R  2360623 9.472e-05  NA
  </code></pre>
  
  <p>Attempt</p>
  
  <p>I have been trying to adjust the code from this question to do this. 
  <a href="https://stackoverflow.com/questions/21622517/awk-lookup-2-files-print-match-and-sum-of-sencond-field">awk Lookup 2 files, print match and Sum of Sencond Field:</a></p>
  
  <p>So far, I have this:</p>
  
  <pre><code>awk -F"\t" '                                       #Set Field separator to     "\t"
  FNR==NR {a[$1];a[$2];a[$3];next}                    #Read data from Reference using field #1, field #2, field #3 as index in to array a
  {if ($1 in a)                               #Test if field #1 in Table is found in a
  {if ($2 &gt; a[$2])
  {if ($2 &lt; a[$3])
  print $0, a[$4]                       #If found, print line of f1.txt with sum and index from array
  else print $0,"NA"     #If not found print line of f1.txt with NotFound
  }
  else print $0,"NA"     #If not found print line of f1.txt with NotFound
  }
  else print $0,"NA"     #If not found print line of f1.txt with NotFound
  }
  ' OFS="\t" Referance.txt  File.txt                       #Set Output field separator to , and read files
  </code></pre>
  
  <p>which produces</p>
  
  <pre><code>2L  1885826 2.638e-08   NA
  2L  1996567 5.12e-05    NA
  2L  2360597 9.472e-05   NA
  2R  2360621 9.472e-05   NA
  2R  2360623 9.472e-05   NA
  </code></pre>
  
  <p>I think I am making a mistake somewhere in the nested if else statements, but I am not sure what I am doing wrong.  Would appreciate any suggestions!</p>