Excel - VLOOKUP returning #VALUE

0
votes

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

1
excelexcel-formulavlookup
Your lookup range $A$1:$O$1 is one row (Typo maybe?). VLOOKUP looks down the first column in a range of several rows. Also, you are looking up the word "Probabilities" (don't quite understand the setup). - chuff
@chuff Right, the table is just one row. It might be a horrible method for what I'm doing. I use "Probabilities" (which is static) to look up the one row that exists and the variable is the index. Basically I need 25.9% to be returned if A2 is 6. - jtsmith1287
You should be able to use OFFSET instead. Usage is: OFFSET(cell_reference, row_offset, column_offset, height_in_rows, width_in_columns). For example, OFFSET($A$1,0,A2,1,1). (INDEX would work as well.) - chuff
Maybe HLOOKUP or INDEX may be more usefull in this caqe - chris neilsen

1 Answers

3
votes

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.