# Thread: Index Match with result coming from 1 of 2 Columns Thanks:  1 Post #5343182 (1) Likes: 0

1. ## Index Match with result coming from 1 of 2 Columns

Hello.. I am trying to get a formula so that when A1 matches a Product ID in Sheet2,Column B, it will
a) return the data from the relevant row in Column D, if the corresponding row in column Q = No or,
b) return the data from the relevant row in Column R, if the corresponding row in column Q = Yes

I've tried lots of variations like these two but none are working so I'm thinking I must be on the wrong track.
=IF('sheet2'!Q:Q ="No", INDEX('sheet2'!D:D,MATCH(A1,'sheet2'!B:B,0)),INDEX('sheet2'!R:R,MATCH(A1,'sheet2'!B:B,0)))

=IFERROR(INDEX('sheet2'!D:D,MATCH(A1,'sheet2'!B:B,0),MATCH('yesnosheet'!A1,'sheet2'!Q:Q,0)),INDEX('sheet2'!R:R,MATCH(A1, 'sheet2'!B:B,0),MATCH('yesnosheet'!A2,'sheet2'!Q:Q, 0)))

2. ## Re: Index Match with result coming from 1 of 2 Columns

Can column Q be anything else or will it always be yes or no

3. ## Re: Index Match with result coming from 1 of 2 Columns

Hi .. it will always be Yes or No

4. ## Re: Index Match with result coming from 1 of 2 Columns

How about
=IF(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="No", INDEX(Sheet2!D:D,MATCH(A1, Sheet2!B:B,0)), INDEX(Sheet2!R:R,MATCH(A1, Sheet2!B:B,0)))

5. ## Re: Index Match with result coming from 1 of 2 Columns

WOW! that's working perfectly thank you so much.

6. ## Re: Index Match with result coming from 1 of 2 Columns

You're welcome & thanks for the feedback

7. ## Re: Index Match with result coming from 1 of 2 Columns

What about just
=VLOOKUP(A1,Sheet2!B:R,3+14*(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="Yes"),0)

Or if you want to stick to all index/match
=INDEX(Sheet2!D:R,MATCH(A1,Sheet2!B:B,0),1+14*(INDEX(Sheet2!Q:Q,MATCH(A1,Sheet2!B:B,0))="Yes"))

## User Tag List

#### Posting Permissions

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