Tracy van der Schyff

#Office365Challenge HLOOKUPS are used to retrieve data from other tables or ranges horizontally. A common field is needed to match and return stated values.

In below example I would like to populate the Unit and Rate automatically from another source, based on the value I enter in column A:

The values I require are captured on another sheet:

Start the formula then select the first cell (A3) which it will try to match on the other sheet, add a semicolon (or comma):

Now navigate to the other sheet and select the table where it must look for the cell, add a semicolon (or comma):

Indicate which row it must return (Product being row 1, unit being row 2) add a semicolon (or comma) then close with false:

Use “$” to fix the table before copying the formula down to B4:B7. Repeat the formula for Rate, asking to return row 3:

Related posts:  Using VLOOKUP in Excel

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.

Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.