Look Who it Was Random video

Excel - Find Matching Value and Return Cells to the Right of Match - Episode 1188

1,753 views · Published 12 March 2010 · 4:38 · Indexed 7 October 2026

Channel: MrExcel.com · 2010 · Education

Watch on YouTube

Microsoft Excel Tutorial: In this Dueling Excel Episode, Find a value in this row and then get the values 1 and 2 columns to the right of that value.

Welcome to another episode of the Dueling Excel podcast! I'm Bill Jelen from MrExcel.com and I'm joined by Mike Girvin, also known as ExcelIsFun on YouTube. Today, we have a question from one of our viewers, Ednen or luvbite38, about performing an offset lookup in Excel.

The task at hand is to find a lookup value in one row, and then retrieve the values in the adjacent columns. To accomplish this, I start by using the =MATCH function to find the lookup value in the adjacent row. By pressing F4 three times, I can lock the lookup value and search for it in the desired row. The function returns the column number where the lookup value is found.

Next, I use the =INDEX function to retrieve the values in the adjacent columns. By pressing F4 three times again, I can lock the range of cells where the values are located. Then, I specify the row number as 1, since there is only one row in this case, and add 1 to the column number to get the value in the column just to the right. This formula can be copied and pasted to retrieve the values in the second adjacent column as well.

Now, let's see how Mike approaches this task. He starts by highlighting the entire data set and using the =INDEX function to retrieve the values in the adjacent columns. By pressing F4 three times, he locks the range of cells where the values are located. Then, he uses the =MATCH function to find the lookup value in the adjacent row. By pressing F4 three times again, he locks the lookup value and searches for it in the desired row. The function returns the column number where the lookup value is found.

To get the values in the adjacent columns, Mike uses the =COLUMNS function, which is a great number incrementer inside a formula. By specifying the starting and ending columns, he can easily increment the column number by 1 for each row. This formula can be copied and pasted to retrieve the values in the second adjacent column as well.

And there you have it! Two different approaches to perform an offset lookup in Excel. We hope you found this tip helpful and we'll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun. Don't forget to like, comment, and subscribe for more Excel tips and tricks. 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) Introduction by Bill and Mike
(00:25) Question from viewer
(00:40) Bill's solution using =MATCH and =INDEX
(01:50) Mike's solution using =INDEX and =MATCH with =COLUMNS
(04:18) Clicking Like really helps the algorithm

This video answers these common search terms:
Column number
Copy and paste
Dueling Excel podcast
Exact lookup
Excel tips
Formula population
Freeze columns
Incrementer formula
Index function
Lookup value
Match function
Row number

 Mike and Bill duel it out in Episode #1188.

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

More from this channel