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
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
-
2:13
Excel Calculate Decimal Hours from two Time Cells - Episode 902
-
3:57
Excel 2007 - Color of Selected Sheets is too much like unselected sheets - Episode 907
-
2:48
Excel - Adding Equals Icon to Excel QAT - Build Formula with Mouse - Episode 911
-
3:30
Excel Grouping Percentages into Buckets for a Pivot Table - Method 2 - Episode 913
-
2:04
Excel Change Color of Selected Cells - Episode 914
-
4:38
Excel - Auto Numbering a List with Frequent Inserts & Deletes - Episode 916
-
2:00
Excel Hiding Data in Plain Site - Episode 919
-
2:20
Excel - Bring Back The Full Excel 2003 Dialogs In Excel! - Episode 920