Indirect function not returning correct value

[iamskippy]

I am trying to use Indirect to return back a scoring value from the table below. When i enter a value into D37, it only returned the closes whole number and not the closest rating.

For example if i entered in 12.80%(no rounding needed) it would return 1.0 and not 1.2



Formula =VLOOKUP(D37,INDIRECT(C2),2)
D37 = Data i want looked up
C2 = Table Name

(Table Below)
Score Rating
1.00% 1.00
6.90% 1.10
12.80% 1.20
18.70% 1.30
24.60% 1.40
30.50% 1.50
36.40% 1.60
42.30% 1.70
48.20% 1.80
54.10% 1.90
60.00% 2.00
61.00% 2.10
62.00% 2.20
63.00% 2.30
64.00% 2.40
65.00% 2.50
66.00% 2.60
67.00% 2.70
68.00% 2.80
69.00% 2.90
70.00% 3.00
71.00% 3.10
72.00% 3.20
73.00% 3.30
74.00% 3.40
75.00% 3.50
76.00% 3.60
77.00% 3.70
78.00% 3.80
79.00% 3.90
80.00% 4.00


Thanks in advance for any help, if i can nail this down my project will be just about done!

[daffodil11]

You missed the True False part of VLOOKUP, which declares exact matches.

=VLOOKUP(D37,INDIRECT(C2),2,0)

or

=VLOOKUP(D37,INDIRECT(C2),2,FALSE)

[Richard Buttrey]

Hi,

It works as expected for me. You'll need to upload your workbook so we can see the problem in context.

[Tony Valko]

Works OK for me.

Do you get correct results for other lookup values?

We may need to see the file to figure out what the problem is.

[iamskippy]

You missed the True False part of VLOOKUP, which declares exact matches.

=VLOOKUP(D37,INDIRECT(C2),2,0)

or

=VLOOKUP(D37,INDIRECT(C2),2,FALSE)

If you leave it with out a 0, True or False it assumes True.


Well this is weird, i tried it in another work book and you guys are right it is working, what could cause it not to work in this book?

[daffodil11]

I was able to get it to work fine as well.



One possible problem is that True, the assumed value, is for non-exact matches.

I always declare False or 0 for LOOKUP and MATCH, unless I'm just approximating statistics.

[iamskippy]

The problem is that True, the assumed value, is for non-exact matches.

I always declare False or 0 for LOOKUP and MATCH, unless I'm just approximately statistics.

thanks for the follow up, however i got it to work with out it, i rebuilt the page ( moved the data over to a clean sheet and its working), i would like to find out why its not working on that one page but working on others....

[Jonmo1]

i would like to find out why its not working on that one page but working on others....

Difficult to say without actually seeing it.
Can you attach a copy of the file?

[Tony Valko]

As Richard noted, we would need to see the file so we can see the problem in context.

On a side note, I won't download files >50kb in size.

[iamskippy]

As Richard noted, we would need to see the file so we can see the problem in context.

On a side note, I won't download files >50kb in size.

I will Clean up that sheet, i think its small enough, never uploaded anything here before is there an attachment option i don't see it above, or am i going to have to move it to an external host?

[Tony Valko]

When you reply click the "Go Advanced" button.

Scroll down and you'll see Manage Attachments. Click that button and go from there.

It's not exactly intuitive which is why I normally use an external host.