Index Match with result coming from 1 of 2 Columns

[tancha]

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)))

 

[jimrward]

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

 

[tancha]

Hi .. it will always be Yes or No

 

[Fluff]

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)))

 

[tancha]

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

 

[Fluff]

You're welcome & thanks for the feedback

 

[Peter_SSs]

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"))