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.
 

Some videos you may like

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,465
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,329
Messages
5,600,994
Members
414,418
Latest member
mightyMagnus

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