Hi,

I was looking at doing a formula like the below;

=IF(ISNUMBER(SEARCH(”P”,D19,3)),(VLOOKUP(D19,'Rate s Summary '!A$2:Q$1573,2,0)),D19)

Basically, I have a code "QAPLI0" in cell D19. What i want to do is convert this to a QAR code which is what my Vlookup is for. Only problem is that when i already have a QAR code in the cell D19 it still brings me back a QAP instead of leaving it was whats already in the cell D19 which is what i though i had down as the "value if false" part of the IF statement.

Am i making sense? I tried to use a FIND instead of SEARCH but got the same result. Am i missing something here?

Any help would be great.

Thanks


Jon

Attached Files

excel help.xlsx (78.3 KB, 11 views)

The columns in your rates table need to be switched. What you are searching for, the QAR code, should be in the leftmost column and what you want returned, the QAP code, should be in some column to the right.

What is it you are trying to do here? Correct codes that are incorrectly entered?

What i am trying to do is conver the QAP code to the QAR code in column 2 in the Rate Summary. However if the reference code is already a QAR code i do not want to change it.

Does that make sense?

This is why i have the logical test on the IF statement referencing the P in the reference cell C5.

=If(Mid(D19,3,1)="P",Left(D19,2) & "R" & Right(D19,3),D19)

If the third character is always either P or R, then the If and Mid are superfluous and you could just use...

=Left(D19,2) & "R" & Right(D19,3)

Unfortunately i cannot do that. I have hundreds of different old paycodes to convert, hence the lookup. The 2nd and 3rd letters change and are not just A, P or R. Its only the 3rd letters that reference a "P" i need to convert.

So what i am reying to say is;

If the ref cell contains a P in the 3rd position vse the lookup. If not, just stick with the existing reference cell.

So i added another code for reference sake.

Thanks,
Jon

Attached Files

excel help.xlsx (78.4 KB, 8 views)

In that case, my first formula should work unless I'm missing something.

=If(Mid(D19,3,1)="P",Left(D19,2) & "R" & Right(D19,3),D19)

To whit, if the third character is P, then use the first two original characters, and R, and the last three characters i.e. only change the third character to R if it originally P.

No lookup needed. Simple replacement schema.

Ok great. I have run that and got all the right results.

Really appreciate your help on this.

Thanks Gebobs.