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.
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:
- Press Alt+F11 to open the VBA editor.
- From within the VBA editor click Insert>Module.
- 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)
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.