I'm doing a hlookup against a value that spans multiple columns. My data is similar to this:

      A      B      C      D 
  ---------------------------  
1|       Col1          Col2
2|     x      y      z      w
3|
4|

In rows 3 and 4 (A3, B3, C3, D3, etc.), I'd like to put formulas that will do an hlookup somewhere else in the workbook. The trick is, I'd like it to look up "Col1" for columns A and B and "Col2" for columns C and D. "Col1" is in A1, but is really A1 and B1 merged. When I reference A1, "Col1" appears, but when I reference B1, the return value is blank.

Any ideas?

Edit
Report