How to Perform Two-Way Lookup in Excel

To look up a value in a table using both rows and columns, you can build a formula that does a two-way lookup with VLOOKUP, MATCH, and INDEX functions. This type of formula can be confusing and difficult to build. However, with the help of Chart GPT, you can generate this type of formula with ease.

Here’s how you can perform a two-way lookup in Excel:

1. Identify the lookup table that includes the lookup column and the columns from which you want to retrieve values.
2. Use VLOOKUP to look up for the exact value in the specified range.
3. Use MATCH function to get the return column number dynamically based on the values in row 1.
4. Use INDEX function to retrieve the value from the return column.

To generate the formula using Chart GPT, follow these steps:

1. Go to openai.com and enter the request in the ‘Send a Message’ text box.
2. Enter ‘Generate an Excel formula using VLOOKUP to look up for the exact value in G3 in the range A2 to D19’ in the message box.
3. Click on ‘Send Message’.

Ch

art GPT will respond to your request with a formula to use and a brief explanation of how to use it in Excel. Review the formula and make any necessary changes to ensure it meets your needs.

To enter the formula in Excel, follow these steps:

1. Select the cell in which you want to enter the formula.
2. Paste the copied formula in the formula bar and press Enter.

Alternatively, you can click in the cell where you want to enter the formula, paste the formula, and press Enter.

Once the formula is entered, the sales value for the specified region and month will be returned.

It’s important to note that VLOOKUP formulas can break when columns are inserted or removed from a table. To avoid this, you can use the INDEX and MATCH functions to build a two-way lookup formula. These formulas will not break even when columns are inserted or deleted.

If you don’t understand the formula, you can ask Chart GPT to explain it. Simply enter ‘Explain’ in the message box, and Chart GPT will provide an explanation of the formula.

The practice Excel file, which includes the formulas generated by Chart GPT, is available for download. You can find the link in the video description.

If you found this video helpful, please subscribe to our channel for more Excel tutorials.