VLookup and HLookup in C# with a DataTable

aendenne — Fri, 29 May 2009 05:39:00 GMT
(Mimicry/Extension of Excel's VLookup/HLookup in C#)

Here I will give you my VLookup and HLookup methods; they have been overloaded to allow flexibility. In Excel the first row or column of values is referred to/searched through. In the HLookup/VLookup functions I wrote, row/column name/index can be specified.

Sometimes a developer may need to dynamically locate a specific text value in order to get an associated data point for that text value (in the same data row or data column). Put another way: these lookup functions allow a developer to determine the contents of one cell by locating an associated cell (at runtime).

I am attaching a demo application that contains examples of HLookup and VLookup and all dependent code. You may download it here: Download Demo App

The Lookup Methods:
   61         /// <summary>
   62         /// Similar to Excel's hlookup. Searches for value (column by column)
   63         /// beginning in the first row of the passed in table and returns 
   64         /// corresponding value from the same (found) column as specified by the
   65         /// matchRow value.
   66         /// </summary>
   67         /// <remarks>
   68         /// If user looks for the 'color' field and the 5th row, 
   69         /// the function will grab data from the color column, 5th row
   70         /// </remarks>
   71         /// <param name="tbl">table to look in</param>
   72         /// <param name="val">value to locate (will pinpoint a row)</param>
   73         /// <param name="lookInRow">
   74         /// row index to look in for specified value, excel uses first row,
   75         /// let us allow a bit more flexibility. (set to 0 for 1st row)
   76         /// </param>
   77         /// <param name="matchRowIdx">index of row to get return data from</param>
   78         /// <param name="partialMatch">whether to stop at a partial match</param>
   79         /// <returns>
   80         /// null if value not found, or data from matching row in same column as found value
   81         /// </returns>
   82         /// <writer>aendenne</writer>
   83         /// <created>5/28/09</created>
   84         /// <edited>5/28/09</edited>
   85         public static string HLookup(DataTable tbl, string val, int lookInRow, 
   86             int matchRowIdx, bool partialMatch)
   87         {
   88             if (tbl == null || tbl.Rows.Count <= 0) return null;
   89             if (string.IsNullOrEmpty(val)) return null;
   90             if (tbl.Rows.Count <= lookInRow || tbl.Rows.Count <= matchRowIdx) return null;
   91 
   92             //still here--try to locate value in specified row:
   93             val = val.Trim().ToUpper();
   94 
   95             DataRow rw = tbl.Rows[lookInRow];
   96             for (int i = 0; i < tbl.Columns.Count; i++)
   97             {
   98                 string fldData = rw.ToString().Trim().ToUpper();
   99                 if (partialMatch && fldData.Contains(val))
  100                     return tbl.Rows[matchRowIdx].ToString();
  101                 if (fldData == val.Trim().ToUpper())
  102                     return tbl.Rows[matchRowIdx].ToString();
  103             }
  104             return null;
  105         }
  106 
  107         /// <summary>
  108         /// Similar to Excel's vlookup. Searches for value (row by row)
  109         /// in one column and returns ENTIRE row where that value was found (first instance)
  110         /// </summary>
  111         /// <param name="tbl">table to look in</param>
  112         /// <param name="val">value to locate (will pinpoint a row)</param>
  113         /// <param name="valColName">name of column to find value in</param>
  114         /// <returns>
  115         /// null if value not found, or data from matching column in same row as found value
  116         /// </returns>
  117         /// <writer>aendenne</writer>
  118         /// <created>5/28/09</created>
  119         /// <edited>5/28/09</edited>
  120         public static DataRow VLookup(DataTable tbl, string val, string valColName)
  121         {
  122             if (tbl == null || tbl.Rows.Count <= 0) return null;
  123 
  124             if (string.IsNullOrEmpty(val) || string.IsNullOrEmpty(valColName))
  125                 return null;
  126 
  127             int valCol = tbl.Columns.IndexOf(valColName);
  128 
  129             //chk that col names do exist in this tbl:
  130             if (valCol < 0) return null;
  131 
  132             foreach (DataRow rw in tbl.Rows)
  133                 if (rw[valCol].ToString().Trim().ToUpper() == val.Trim().ToUpper())
  134                     return rw;
  135 
  136             return null;
  137         }
  138 
  139         /// <summary>
  140         /// Similar to Excel's vlookup. Searches for value (row by row)
  141         /// in one column and returns value from matching column in same row.
  142         /// </summary>
  143         /// <param name="tbl">table to look in</param>
  144         /// <param name="val">value to locate (will pinpoint a row)</param>
  145         /// <param name="valColName">name of column to find value in</param>
  146         /// <param name="matchColName">name of column to get return data from</param>
  147         /// <returns>
  148         /// null if value not found, or data from matching column in same row as found value
  149         /// </returns>
  150         /// <writer>aendenne</writer>
  151         /// <created>5/28/09</created>
  152         /// <edited>5/28/09</edited>
  153         public static string VLookup(DataTable tbl, string val, 
  154             string valColName, string matchColName)
  155         {
  156             if (tbl == null || tbl.Rows.Count <= 0) return null;
  157 
  158             if (string.IsNullOrEmpty(val) || string.IsNullOrEmpty(valColName)
  159                 || string.IsNullOrEmpty(matchColName))
  160                 return null;
  161 
  162             int valCol = tbl.Columns.IndexOf(valColName);
  163             int matchCol = tbl.Columns.IndexOf(matchColName);
  164 
  165             //chk that col names do exist in this tbl:
  166             if (valCol < 0 || matchCol < 0) return null;
  167 
  168             foreach (DataRow rw in tbl.Rows)
  169                 if (rw[valCol].ToString().Trim().ToUpper() == val.Trim().ToUpper())
  170                     return rw[matchCol].ToString();
  171 
  172             return null;
  173         }
  174 
  175         /// <summary>
  176         /// Similar to Excel's vlookup. Searches for value (row by row)
  177         /// in one column and returns value from matching column in same row.
  178         /// </summary>
  179         /// <param name="tbl">table to look in</param>
  180         /// <param name="val">value to locate (will pinpoint a row)</param>
  181         /// <param name="valColIdx">index of column to find value in</param>
  182         /// <param name="matchColIdx">index of column to get return data from</param>
  183         /// <param name="partialMatch">whether to stop at a partial match</param>
  184         /// <returns>
  185         /// null if value not found, or data from matching column in same row as found value
  186         /// </returns>
  187         /// <writer>aendenne</writer>
  188         /// <created>5/28/09</created>
  189         /// <edited>5/28/09</edited>
  190         public static string VLookup(DataTable tbl, string val, int valColIdx, 
  191             int matchColIdx, bool partialMatch)
  192         {
  193             if (tbl == null || tbl.Rows.Count <= 0) return null;
  194 
  195             if (string.IsNullOrEmpty(val) || tbl.Columns.Count <= valColIdx ||
  196                 tbl.Columns.Count <= matchColIdx)
  197                 return null;
  198 
  199             val = val.Trim().ToUpper();
  200             foreach (DataRow rw in tbl.Rows)
  201             {
  202                 string fldData = rw[valColIdx].ToString().Trim().ToUpper();
  203                 if (partialMatch && fldData.Contains(val))
  204                     return rw[matchColIdx].ToString();
  205                 if (fldData == val.Trim().ToUpper())
  206                     return rw[matchColIdx].ToString();
  207             }
  208             return null;
  209         }
Foo Theory

VLookup and HLookup in C# with a DataTable
