# multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

1. ## multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Am trying to lookup a value that matches multiple rows and column criteria. Eg:

January February March April
Leather Black Big Bag 100 200 300 400
Leather Red Small Bag 200 300 400 500
Leather Green Big Bag 300 400 500 600
Leather Yellow Small Bag 400 500 600 700
Cotton Black Big Bag 500 600 700 800
Cotton Red Small Bag 600 700 800 900
Cotton Green Big Bag 700 800 900 1000
Cotton Yellow Small Bag 800 900 1000 1100

Lookup value for the following combination:

Cotton Green Big Bag January
Cotton Red Small Bag March
Leather Yellow Small Bag February

2. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Enter this formula in F13 and copy down

=INDEX(\$E\$4:\$H\$11,MATCH(B13&C13&D13,INDEX(B\$4:B\$11&C\$4:C\$11&D\$4:D\$11,0),0),MATCH(E13,E\$3:H\$3,0))

3. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Try this...

Entered in F13 and copied down:

=SUMIFS(INDEX(E\$4:H\$11,0,MATCH(E13,E\$3:H\$3,0)),B\$4:B\$11,B13,C\$4:C\$11,C13,D\$4:D\$11,D13)

4. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Dear Alkey,

It worked perfectly.....Thanks so much for your super quick help.

Really appreciate it.

Regards...

5. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Range concatenation is inefficient.

If you can do it without range concatenation then you should try some other method.

7. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

You're welcome. Thanks for the feedback!

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

8. ## Re: multiple row and column criteria lookup - may be INDEX fn with row and column MATCH

Formula:
`Please Login or Register  to view this content.`

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