Your Lookup Table Can Go Across

April 13, 2022 - by Bill Jelen

Problem: Someone built this lookup table going across the worksheet. How can I use VLOOKUP?

The lookup table is built going across instead of down. F2:Q2 contains Jan through Dec. F3:Q3 contain the bonus rate for each month. — Figure 419. The table is going the wrong way.

Strategy: The “V” in VLOOKUP stands for Vertical Lookup. Excel also offers an HLOOKUP for horizontal lookup tables. If you are in a bizarre mood, you could actually use HLOOKUP: =HLOOKUP(B3,$F$2:$Q$3,2,FALSE).

Alternate Strategy: You will most likely do what I and every other person using Excel does: Copy F2:Q3. Select cell F5. Do Paste, Transpose. This turns the lookup table back so it is vertical. Then you can do a VLOOKUP.

This article is an excerpt from Power Excel With MrExcel

Title photo by Ryoji Iwata on Unsplash