Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
1  
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
add comment

3 Answers

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"?
share|improve this answer
1  
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
add comment

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.

share|improve this answer
add comment

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)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.