Why doesn't getting a value from a named range work, only for this specific value?

Question

I have a named range called A2Bounds25:

Grade	Percentage
A*	80
A	71
B	60
C	49
D	38
E	27

I pull the percentage from A2Bounds25:

=VLOOKUP(F74, (INDIRECT($K$3)),2)

where F74 contains the grade I want to look up, and K3 the string A2Bounds25. This works for all grades except A.

Even if I change A to any other unused letter (and update A2Bounds25 accordingly) it still refuses to pull the percentage for an A. All other grades pull the correct percentage, and I have used the same formula in each cell other than changing F74 to F75 and so on. If I replace the cell reference with "A" it still will not work.

How can I correct this?

=VLOOKUP(SUBSTITUTE(F74,"*","~*"), (INDIRECT($K$3)),2,False) — Scott Craner
– Scott Craner, Commented Oct 21 at 18:29

user4157124 · Accepted Answer · 2025-10-21 18:29:49Z

3

Set the range lookup option to FALSE:

=VLOOKUP(F74, (INDIRECT($K$3)),2,FALSE)

edited Oct 21 at 18:29

user4157124

3,00021 gold badges33 silver badges48 bronze badges

answered Oct 21 at 17:44

Natre

2241 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Nitesh Halai · Accepted Answer · 2025-10-21 18:57:23Z

3

The reason the formula doesn't work is because the lack of FALSE the values of 'Grade' column are not sorted. A comes before A* when it comes to sorting the values in an ascending order, so when the VLOOKUP is lookup up the value with the exact match (FALSE) argument is missing, it will not be able to 'find' the value A, since its already overtaken by A*

answered Oct 21 at 18:57

Nitesh Halai

9678 silver badges21 bronze badges

Comments

user2463758 · Accepted Answer · 2025-10-21 18:56:33Z

1

Thank you to those who suggested setting the range lookup to FALSE. This has fixed it.

=VLOOKUP(F74, (INDIRECT($K$3)),2,FALSE)

answered Oct 21 at 18:56

user2463758

893 silver badges13 bronze badges

1 Comment

Community Oct 21 at 18:59

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

P.b · Accepted Answer · 2025-10-21 19:26:29Z

0

There are 2 things why you may find wrong results, or an error.

The first issue is the match type argument (as already mentioned by others).

The second issue is the asterisk being a wildcard character in VLOOKUP. To search for an actual * you need to tell Excel to read it as the actual character by adding ~ in front of the sign.

This way the formula also works if A would be listed before A*:

=VLOOKUP(SUBSTITUTE(F74,"*","~*"),INDIRECT($K$3),2,0)

edited Oct 21 at 19:26

answered Oct 21 at 19:20

P.b

12k2 gold badges15 silver badges34 bronze badges

2 Comments

user2463758 Oct 22 at 21:44

Thank you for this. I had considered the wildcard character when I was trying to debug this myself, but changing the grades to arbitrary letters without the * didn’t fix the issue either. As you identified, it was the match type that fixed it for me. Thanks again!

P.b Oct 22 at 22:00

If you don't use an argument it defaults to approximate match. You can use FALSE, 0, or use a comma without mentioning the false or zero, to make it "undefault" to exact match: =VLOOKUP(SUBSTITUTE(F74,"*","~*"),INDIRECT($K$3),2,)

Collectives™ on Stack Overflow

Why doesn't getting a value from a named range work, only for this specific value?

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related