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.

I have got two columns, let say column A and B. In Column A, I have P/L Value. In column B, i have got percentage corresponding to each value. Now i need to find out a way to pick the P/L value whose percentage value is 5%. Please note, Column B can containg 5% value or it may not. It can have value between 4 and 5%, for example. In that case, I need to calculate P/L value for 5%. Please help.

share|improve this question
    
if one of the responses below worked for you, you should click the checkmark to the left of the response to accept the solution. If not, let us know if it didn't work. –  variant Aug 10 '10 at 13:30

2 Answers 2

You possibly want the VLOOKUP function, although it requires your lookup value to be the first column in your lookup table, so you would have to have the percentage values to the left of the P/L Values. You will also need to have the lookup column sorted in acsending order.

It supports "range" lookups, which will find a match in the case that exactly 5% is not given in the lookup column.

Example

Take the following in a sheet:

      [A]      [B]
 [1]  4.00%    100
 [2]  4.32%    110
 [3]  4.70%    120
 [4]  5.12%    130
 [5]  5.80%    140

The formula =VLOOKUP( 0.05 , A1:B5 , 2 , TRUE) , this will return the value 120 as this is closest value for with column A does not exceed 5%.

share|improve this answer

I think a VLOOKUP is your best bet, but if you don't want to have to move your columns, you could use OFFSET with MATCH:

=OFFSET($A$1,MATCH(A8,$B$1:$B$5,0)-1,0,1,1)

Where:

  • A1 is the first cell of your P&L numbers
  • A8 is where the percentage you want to look up is located (could be any cell or value)
  • B1:B5 is the range in which your percentages are located
  • the 0 in the match function specifies that the MATCH needs to be exact

It works by offsetting from cell A1 by:

  • However many rows down the "matched" value is (less 1, because you need to account for the fact that the cell you're offsetting from (A1) is part of your data set
  • 0 columns over (had you used B1 as your reference cell, it would be -1 because you'd need to offset to the left by one column)

And the range you need to return is 1 cell high and 1 cell wide.

share|improve this answer
    
+1 Nice, I'll have to remember this trick next time I don't want to or can't refactor my data for a standard lookup. –  DMA57361 Aug 9 '10 at 12:46

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.