To pick a particular value from a column

Question

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.

Answer 1

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%.

Answer 2

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.