# To pick a particular value from a column

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.

-
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

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

-

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.

-
+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