tech on the net
Home About Us Feedback Site Map

Microsoft

Access Excel Word

Database

SQL Oracle / PLSQL SQL Server MySQL MariaDB PostgreSQL

Web Development

HTML CSS Color Picker

Language

C Language

More

ASCII Table Linux UNIX Java Clipart Joke of the Moment

Other Sites

CheckYourMath BigActivities DigMinecraft
Share this page:

MS Excel: Use an array formula to perform a two criteria lookup in Excel 2003/XP/2000/97

Question: I have the following table in Microsoft Excel 2003/XP/2000/97:

Column A Column B Column C Column D Column E
EL_ID LD_ID nx ny qxy
3000 L001 -10.8 -280.9 981.0
3000 L002 -145.0 -315.0 441.1
3000 L003 -122.2 -315.8 451.2
3001 L001 -6.4 -135.6 -161.8
3001 L002 -8.2 -154.0 -157.9
3001 L003 -8.3 -154.7 -167.9

I’m trying to create a formula in Excel that returns the corresponding qxy value, given a EL_ID and LD_ID value.

For example, I need the formula to return a qxy value of -161.8, given an EL_ID=3001 and LD_ID="L001".

How can I do this?

Answer: This can be done in Excel with an array formula.

Let's look at an example.

Microsoft Excel

In cell A10, we've created the following array formula:

=INDEX(E2:E7,MATCH(3001,IF(B2:B7="L001",A2:A7),0))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(E2:E7,MATCH(3001,IF(B2:B7="L001",A2:A7),0))}

What this formula does is perform a two criteria lookup. It looks for a value of 3001 in cells A2:A7 and "L001" in cells B2:B7, and returns the corresponding value from column E (ie: E2:E7). In this example, it returns a value of -161.8 from column E.

If you wanted to instead lookup an EL_ID=3000 and LD_ID=L003, as in this example:

Microsoft Excel

In cell A10, we've created the following array formula:

=INDEX(E2:E7,MATCH(3000,IF(B2:B7="L003",A2:A7),0))

When creating your array formula, you need to use Ctrl+Shift+Enter instead of Enter. This creates {} brackets around your formula as follows:

{=INDEX(E2:E7,MATCH(3000,IF(B2:B7="L003",A2:A7),0))}

What this formula does is perform a two criteria lookup. It looks for a value of 3000 in cells A2:A7 and "L003" in cells B2:B7, and returns the corresponding value from column E (ie: E2:E7). In this example, it returns a value of 451.2 from column E.