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

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.


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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