Conditional format using Vlookup

mwardle

New Member
Joined
Sep 29, 2009
Messages
9
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,702
Office Version
  1. 365
Platform
  1. Windows
Hi, you could give this a try with the CF:

=ISNUMBER(MATCH(LEFT(H4,LEN(H4)-1),Sheet1!A:A,0))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,681
Messages
5,838,776
Members
430,568
Latest member
bortey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top