Index & match formula with if statement

[kiwikiki718]

Is there a way to perform a index match function if 2 conditions are met.

I am able to get the correct results if I use the below function alone

=INDEX(sheet2!C2:C2318, MATCH(1,(sheet2!A2:A2318=sheet1!X567)*(sheet2!B2:B2318="Replay"),0))

But I need to execute this function only if sheet1 cell x567 contains the word “Rep” and cell V567 = “Replay”

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what should happen if either of those cells do not have those values?

 

[kiwikiki718]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also what should happen if either of those cells do not have those values?

If either cells does not meet both conditions, then to just display a blank cell

 

[Fluff]

And the answer to my other question?

 

[kiwikiki718]

And the answer to my other question?

totally missed that. I have followed your suggestion and updated my account to reflect such details. but I am using office 365 on a Windows pc

 

[Fluff]

Thanks for that.
How about

=IF(AND(X567<>"rep",V567<>"Replay"),"",TAKE(FILTER(Sheet2!C2:C2318, (Sheet2!A2:A2318=X567)*(Sheet2!B2:B2318=V567),""),1))

 

[kiwikiki718]

Thanks for that.
How about

=IF(AND(X567<>"rep",V567<>"Replay"),"",TAKE(FILTER(Sheet2!C2:C2318, (Sheet2!A2:A2318=X567)*(Sheet2!B2:B2318=V567),""),1))

Thanks but that formula does not return the correct value. results show blank

 

[Fluff]

In that case can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.