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.
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) 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.
If you use IF() function it’s a trivial task to obtain what you are asking.