CODEWITHSUNDEEP
excelexcel-formulavlookup
jtsmith1287
jtsmith1287

Reputation: 1159

Excel - VLOOKUP returning #VALUE

Yesterday I asked this question. I tried out another VLOOKUP formula seeing how useful it was, but have run into a very strange error I can't seem to fix. I have the following row of data

Probabilities| 1%, 1.9%, 4.6%, 9.3%, 16.2%, 25.9%, 37.5%, 50%, 62.5%, 74.1%, 83.8%, 90.7%, 95.4%, 98.1%

Each item is in it's own cell on a separate sheet called "Probability". On my first sheet I have =VLOOKUP("Probabilities",Probability!$A$1:$O$1,A2+1,FALSE) where A2 is a number between 3 and 17. If A2 is 1, then I get the row header, "Probabilities". If A2 is >= 2 I get error: Argument out of range: <Whatever A2 is +1>. I've tried everything from adding headers, changing the tables values to just integers or specifically ="<some text>" so see if it didn't like the % symbol. The cell A2 is the result of another formula, so I tried to just type the numbers directly into the cell as well, and replacing A2 in the formula with a number. I'm guessing the issue is not with the index. But I just don't know what. Thanks in advance for the help. Let me know if more info is needed

Upvotes: 0

Views: 4729

Answers (1)

chuff
chuff

Reputation: 5876

If I understand your question correctly, you want to look up a specific probability from a one-row table in the sheet "Probability". This table has a label in cell A1 and then cells with probability amounts spanning the columns B through O. A formula in cell A2 computes an index number for the nth value in the probability table. You are trying to find a formula that will return that nth value.

This should work:

  =OFFSET(Probability!$A$1,0,$A$2,1,1)

The syntax for OFFSET is "OFFSET(cell_reference, row_offset, column_offset, height_in_rows, width_in_columns)", where cell_reference is an anchor cell and the row and column offsets indicate how many cells down (up if row offset is negative) and to the right (left if column offset is negative) you want to return a value.

Your formula actually should work (at least it worked on my machine). I did get a #REF! error when I used an index value that was more than the number of columns in the probability table, an #N/A error when the value was a string, and a #VALUE error when A2 was the sum of a number and a string.

Upvotes: 3

Related Questions