Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
Are you looking for a 'standard' excel function, or is vba OK? My first suggestion would be to write a little vba function that uses find to find your 'testN' cell and return the cell 1 column to the right. – DaveParillo Mar 15 '10 at 3:41
I would recommend you always consider the structure of your data first. It seems you should have a test identifier column and then the column with each test's specified numeric value. Any way you can change how your table is generated? Seems that would work better than trying to accommodate your current table. Or look at answers below of course. – mindless.panda Mar 15 '10 at 17:51
The trouble with vba is that I have never used it. How hard is it to integrate with the built in functions? – Matthew Scouten Mar 17 '10 at 3:46
Can Test2 appear in the first column.. or are all the lookup values unique? – Mark Nold Mar 18 '10 at 14:06
A lookup value should be unique. – Matthew Scouten Mar 26 '10 at 14:44
up vote 1 down vote accepted

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


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
 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.

share|improve this answer
If it was really 2 columns that would work, but the actual data has 12 or so. a little unwieldy. – Matthew Scouten Mar 17 '10 at 3:58

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

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .