I have 2 sheets, one containing a set of Masterdata with cells containing data in a format similar to the following UNIN_PRO_ACC_14A, but it can vary in character length around 100 rows.
On a second sheet we input various data, one column will contain UNIN_PRO_ACC_14A, around 400 rows in total however the difficulty is that we could have multiple items of the same type, where this happens the string is incremented on the last character to UNIN_PRO_ACC_14B and so on.
What I’m trying to do is create a conditional format to colour the cell where no match is found in Masterdata for all the data string but exclude the last character from the lookup in both the input and Masterdata
I can get a vlookup to work and return data but cannot get it to work with a conditional format using =VLOOKUP(LEFT(H4,LEN(H4)-1),Sheet1!A:D,1,FALSE)
Any help will be appreciated Thank you in advance for your time and help.
On a second sheet we input various data, one column will contain UNIN_PRO_ACC_14A, around 400 rows in total however the difficulty is that we could have multiple items of the same type, where this happens the string is incremented on the last character to UNIN_PRO_ACC_14B and so on.
What I’m trying to do is create a conditional format to colour the cell where no match is found in Masterdata for all the data string but exclude the last character from the lookup in both the input and Masterdata
I can get a vlookup to work and return data but cannot get it to work with a conditional format using =VLOOKUP(LEFT(H4,LEN(H4)-1),Sheet1!A:D,1,FALSE)
Any help will be appreciated Thank you in advance for your time and help.
