Excel Table lookup with indexes in multiple columns

Question

I have a Table in Excel the looks about like this:

Test1   100 test2   200
Test3   110 test4   210
Test5   120 test6   220

I want a function that, given test 1 as input, will return 100 AND given test 2 will return 200

LOOKUP, (V/H)LOOKUP, and INDEX all insist on a single contiguous array for the index column.

Answer 1

Assuming the values you want returned are always numeric I think this would work:

=IFERROR(VLOOKUP(INPUTCELL,$A$1:$B$3,2,0),0)+IFERROR(VLOOKUP(INPUTCELL,$C$1:$D$3,2,0),0)

This will only work in Excel 2007, but can be modified for 2003. There might be a sexier way, but its the first I thought of. Note that if 'Test 1' is in both columns, as an example, then it will add both corresponding values.

---

Updated answer: As other commenters have suggested, I would re-evaluate the layout of your spreadsheet. Assuming you can't change it, the only scalable solution I see is via VBA. I am not a VBA expert, so there are likely more efficient ways to code this solution, but this is what I got:

1. Press Alt+F11 to open the VBA editor.
2. From within the VBA editor click Insert>Module.
3. Paste the following code:

 Public Function GetValue(rngSearch As Range, rngInput As Range) As Variant
      Dim cell As Variant
      For Each cell In rngSearch
        If cell.Value = rngInput.Value Then
          GetValue = cell.Offset(0, 1)
          Exit For
        End If
      Next
 End Function

4 In order to use the function type "=GetValue(SearchRange, InputCell)" (sans quotes) into the desired cell with InputCell being the cell that says 'Test 1', 'Test 2', etc. and SearchRange being the area you are searching in.

You will need to have macros enabled for this to work.

Answer 2

If you use IF() function it's a trivial task to obtain what you are asking.