# Index Match in an Array 2 Criteria

1. ## Index Match in an Array 2 Criteria

I am trying to use an index match function in an array to return a value. However, I am getting an error. See example below and the formula I am using.

1 2 3
a red orange yellow
b green blue indigo

I want to return "Blue"

I think this requires an array formula with the critiera "b" and "2" so it knows to return Blue any sugguestions on the formula I would use. My 2 criteria index(match) array function returns an #NA error.

2. ## Re: Index Match in an Array 2 Criteria

Dendrinos2,

 1 2 3 a red orange yellow b green blue indigo

Assuming that table is in cells A1:D3 with row 1 being headers and column A being headers (so actual data is in cells B2:D3), you can get the "blue" result with this formula:
``Please Login or Register  to view this content.``

3. ## Re: Index Match in an Array 2 Criteria

Here's another one...

Data in the range A1:D3...

.........1............2............3...
a......red.......orange.....yellow
b......green....blue.........indigo

Lookup values:

F1 = B
G1 = 2

Then, this formula entered in H1:

=VLOOKUP(F1,A1:D3,MATCH(G1,A1:D1,0),0)

If your column headers really are the numbers 1, 2 and 3 then try this version:

=VLOOKUP(F1,A1:D3,G1+1,0)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1