# Index-Match based on 3 criteria

1. ## Index-Match based on 3 criteria

Dear all,

I am currently using the following Index-Match formula to return values based on one criteria:

The condition check is: "IF(\$E\$1:\$E\$2000=\$D\$5;" where \$D\$5 is the name of a city. I wish to include to more criteria, to further narrow the values which are returned. I need to do a check on additionally two values which are located in \$E\$5 & \$F\$5.

So basically, the user selects 3 values from a dropdown and the index-match will return the corresponding values. I am having difficultes including the 2 other critieria. I've tried with an AND function:

SMALL(IF((AND(\$E\$1:\$E\$2000=\$D\$5);(\$F\$1:\$F\$2000=\$E\$5);(\$G\$1:\$G\$2000=\$E\$5));ROW(\$E\$1:\$E\$2000);""). This however, does not work.

Any ideas would be highly appreciated.
Thanks.

2. ## Re: Index-Match based on 3 criteria

Hi johnelbek,

Try :-

IF((\$E\$1:\$E\$2000=\$D\$5)*(\$F\$1:\$F\$2000=\$E\$5)*(\$G\$1:\$G\$2000=\$E\$5))

Include * as show above.

Regards,
DILIPandey
<click on below * if this helps>

3. ## Re: Index-Match based on 3 criteria

I think the & operator is the answer, like this for example: =INDEX(\$B\$3:\$E\$3,MATCH(B4&B5,\$B\$1:\$E\$1&\$B\$2:\$E\$2,0))
Found here: http://support.microsoft.com/kb/59482

4. ## Re: Index-Match based on 3 criteria

Thanks, dili. That did the trick!

5. ## Re: Index-Match based on 3 criteria

You are welcome....

Cheers

Regards,
DILIPandey
<click on below * if this helps>

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