Excel - Dueling Excel- Price Beneath Lookup Value - Episode 1424

1,593 views · Published 9 September 2011 · 6:37 · Indexed 22 October 2026

Channel: MrExcel.com · 2011 · Education

Watch on YouTube

Microsoft Excel Tutorial: Dueling Excel- Price Beneath Lookup Value.

Welcome back to another episode of Dueling Excel, where Mike Girvin from Excel is Fun and I, Bill Jelen from MrExcel, tackle tough Excel challenges. In this episode, we received a question from a viewer about data validation and conditional formatting. The challenge was to set up a data validation that would only allow values below a certain limit, based on a lookup table. This one really threw us for a loop, but we were determined to find a solution.

At first, I thought using VLOOKUP and conditional formatting would be the way to go, but then we had to figure out how to incorporate data validation into the mix. After some trial and error, we came up with a solution using a custom formula in the data validation. We also discovered a handy trick for copying and pasting data validation rules.

Mike had a different approach, using conditional formatting to highlight cells that violated the limit set in the lookup table. He also shared a helpful tip for building the true/false formula in the cells first before applying it to the conditional formatting. We were both impressed with each other's solutions and the different perspectives we brought to the challenge.

We hope this episode has provided some valuable insights and techniques for using data validation and conditional formatting in your own Excel projects. Don't forget to tune in next week for another exciting Dueling Excel podcast from MrExcel and Excel is Fun. Thanks for watching!

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Table of Contents:
(00:00) Get Price Below Lookup Value
(00:12) Attempted Solutions
(01:18) Custom Error Alert
(02:37) Alternative Solution using Conditional Formatting
(05:26) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial


This video answers these common search terms:
Applying conditional formatting to multiple cells
Array constants in Excel data validation
Conditional formatting in Excel
Copy and paste special in Excel
Custom error alerts in data validation
Data validation in Excel
Locking cell references with F4 in Excel
Managing rules in conditional formatting
True/false formulas in conditional formatting
Using formulas in data validation
VLOOKUP function in Excel
YouTube Excel tutorials


Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1153102/


Today, a viewer from YouTube asks how to make sure that a price entered is less than the upper limit in a lookup table. Mike and Bill compare two different ways, one using Data Validation and one using Conditional Formatting. Learn Excel from MrExcel

More from this channel