# referencing cells in other columns

Lets say I am in column 6, row 10.

Column 4 data is one letter per cell, something like this: a,b,a,c,d,e,b,c,f,d

I want a formula that looks up the value of Column 4, row 10 ... gets d.

Then checks whether an earlier cell contains d ... in this case yes, in row 5.

Then goes to Column 2, row 10, and gets the value there ... say 15.

Then goes to Column 2, row 5, and gets the value there ... say 5.

Then does the calculation (15-5) and inserts the value 10 in column 6, row 10.

Is this possible in Excel ?

It would be great if it could be done.

Thank you.

-
What if there are 3 "d" values in column 4? What if there's only that 1 "d" in row 10? –  hyperslug Aug 12 '10 at 7:58

For this solution, one dummy column (G:G) is added.

Formula:
`G1` contains `=B1` to copy the value to the right hand side of column D (for `vlookup`). Copy it down to `G2:G10`

`F1` contains `=B1-VLOOKUP(D1,D:G,4,FALSE)`. Copy it down to `F2:F10`. It subtracts the value of the first matching row from B1.
(Note: `4` means column G is the 4th column in the range `D:G`)

``````   |   A   |   B   |   C   |   D   |   E   |   F   |   G
---+-------+-------+-------+-------+-------+-------+-------
1 |       |       |       |   a   |       |     0 |     0
2 |       |       |       |   b   |       |     0 |     0
3 |       |       |       |   a   |       |     0 |     0
4 |       |       |       |   c   |       |     0 |     0
5 |       |     5 |       |   d   |       |     0 |     5
6 |       |       |       |   e   |       |     0 |     0
7 |       |       |       |   b   |       |     0 |     0
8 |       |       |       |   c   |       |     0 |     0
9 |       |       |       |   f   |       |     0 |     0
10 |       |    15 |       |   d   |       |    10 |    15
``````

The method above calculates `Value of current row - Value of the FIRST MATCHING ROW` no matter there are 1 "d", 2 "d", 3 "d" or more.

As mentioned by @hyperslug, please clarify:

• What if there are 3 "d"?
• What if there is only 1 "d"?
-
I've always wondered why Microsoft always say that the look up column should be sorted otherwise bad things may happen, rather than tell us what happens when VLOOKUP is used with unsorted data. –  Neal Aug 12 '10 at 9:57

So, just to clarify.

For any row with a certain value in it perform a mathematical calculation and output the answer a the end of the row?

Will there only ever be two letters that are the same per cell, and is it always the highest cell number minus the smallest cell number.

If so then it should be easy enough, so long as you can first sort by letter, then by index (to sort by index just have an initial 1,2,3...n+1 as a column. This way you will have something like. a,a,b,b,c,c,d,d,e,e,f,f I am not 100% on the syntax of excel but this should hopefully give you a starter for 10.

-

I would split the problem down and use some extra columns for intermediate steps, muchas you have in your explanation. You can then either hide the intermediate calculations or combine them into one big complicated formula if necessary.

So, starting in the 7th collumn, away from where you want the answer, and assuming you only want to deal with the first occurance, G10:

=MATCH(D10,D\$1:D9,0)

H10:

=OFFSET(B10,G10-ROW(G10),0)

Then F10 can be:

=B10-H10

Putting it together gives one formula:

=B10-OFFSET(B10,MATCH(D10,D\$1:D9,0)-ROW(B10),0)

If you don't want errors you can wrap everything in an `IFERROR` function.

More elegantly, and if you want to remove all occurences of "d", or there is only one other occurrence, you could use the formula:

=B10-SUMIFS(\$B\$1:B9,\$D\$1:D9,D10)

-