VLOOKUP not working when there is more than one entry of the same value

[dominiquebk]

Hiya,

I am using VLOOKUP to pull day rates into a sheet - this is against a set of pre-defined role codes.

My formula is the following: =VLOOKUP($D10,Rates!B3:D26,3,FALSE)

I am facing an issue with the formula not working when two or more of the same role code are used.

How do I fix the issue?

 

[Joe4]

What do you want it to do when there are multiple matching values?

If you want to return all of them, the easiest way is to use the new FILTER function, available in Excel 2021 and 365.
See: FILTER function - Microsoft Support

 

[dominiquebk]

So:

So the user selects the 'role' code from a predefined drop down list, the rate column should then populate with the appropriate 'rate' defined in another sheet. This is all i want it to do.

However, when I select a duplicate role code (see other picture), it errors. It doesn't always do this. For example, when i select another DR1, it doent give me an error.

Does that make sense?

 

[Vogateer]

Looks like you may need to make it a fixed reference. Does adding $ signs to all those formulas help?

=VLOOKUP($D10,Rates!$B$3:$D$26,3,FALSE)

 

[dominiquebk]

Looks like you may need to make it a fixed reference. Does adding $ signs to all those formulas help?

=VLOOKUP($D10,Rates!$B$3:$D$26,3,FALSE)

That's worked! Thank you so much.

Just so I know for next time, what exactly does this mean - making it a fixed reference?

 

[Joe4]

That's worked! Thank you so much.

Just so I know for next time, what exactly does this mean - making it a fixed reference?

If you do not lock it, when you copy it down, your lookup range will shift down one row each time, i.e.

=VLOOKUP($D10,Rates!B3:D26,3,FALSE)

when copied down one row will change to:

=VLOOKUP($D10,Rates!B4:D27,3,FALSE)

Notice how your lookup range is moving down. You want to lock it in place.
Adding a "$" in front of a column or row reference will lock that in place as you copy a formula to another range.