# Open Office Calc - What formula can be used to find a value in a column based on another value being reached in a different column

So I have 2 separate columns (Column A / B for this matter) which use different formulas to increment for approximately different amounts. I need a formula / equation which when column B reaches a certain value in a row (say the value is reached in row 5 and the value I want is 4) it displays the value currently in row 5 of column A. So in the Example below Im looking for when column B reaches 4 (Which is in row 5) so it will display 10 because that is the the value in row 5 of column A.

```Column B Column A 0 2 1 4 2 6 3 8 4 10```

I have tried using an INDEX Match equation as follows
`INDEX(A1:A6;MATCH(4;B1:B6;0)).`

However with this I am getting a #N/A error. What should I be using to correct this?

• This is a bit ambiguous. Can you edit the question to add enough of an example so people can see the values in context and the action you want to happen? – fixer1234 Dec 26 '16 at 19:20

The function you're looking for is VLOOKUP. It returns a value from a table column, in the row found by lookup in the first column.

Syntax:

`VLOOKUP(lookupvalue; datatable; columnindex; mode)`

If you're looking for the value `6` in column A, and your table is in the range `A1:B99`, you should use:

``````VLOOKUP( 6; \$A\$1:\$B\$99; 2; FALSE)
``````

It will return the value in column B where column A has the first 6.

• I have tried using a VLOOKUP method and it still doesn't work (It keeps throwing errors at me. I have read using an INDEX MATCH method would work and have one as follows. INDEX(R5:R6007;MATCH(6;X5:X6007;0)). What im saying here is look for the row in R column which when a value of 6 is found in a row in column X then display the column R value. However I now get a #N/A error and have ran out of solutions. What have I done wrong? – user2041029 Dec 30 '16 at 22:51
• Could you please edit your question showing the values in your sheet and the expected result? – Mariano Dec 30 '16 at 22:56
• Question has been edited for better explanation and has an example of what im trying to do – user2041029 Dec 31 '16 at 13:15