Vlookup with IF error

[josh-mediwatch]

HI I am using a Vlookup to take text fields ('R-Phrases') that relate to codes (R codes) in column F

In the attached spreadsheet this works for codes in column B & C but when I get to column D it is not retrieving the right 'phrase'. For instance R36/37/38.

I have a custom format on columns B,C,D & E so it puts the R and / in but this should not effect the value that the formula is looking for.

I have evaluated the formula and can see why it isnt working as it finds the right value (R code)

Can someone help me with the formula in column F as it wont work when related to column D

Many Thanks

[JohnTopley]

Sorry I can't find any formulas! (in F of Sheet1?)

[Jacc]

There are no formulas. If you include what you have it will be easier to figure out what you want.

[Jonmo1]

Sorry I can't find any formulas! (in F of Sheet1?)

There are no formulas.

The formulas are in column F.


Anyway, you've omitted the 4th argument of the vlookups.
When that argument is omitted, TRUE is assumed
When TRUE is used, the data in the lookup range MUST be sorted in Ascending order by the left column of that range.
Your data in column B of the R-Phrases tab is NOT sorted in ascending order.


To resolve, Either:
1) Sort the data on R-Phrases in ascending order on column B
2) Add FALSE as the 4th argument of each vlookup

[Jacc]

The formulas are in column F.

Ha ha... NOW they are. I think OP replaced the attachment.

[josh-mediwatch]

Thanks knew it would be something simple just needed a false

[Jonmo1]

You're welcome.