Linking data using IF, INDEX and MATCH

[Colin39]

Hi,
I have 2 tabs on a spreadsheet.
The first contains product codes and quantities and a reason code for shortages.
The second tab shows the buyer against each code
I am trying to return the quantity from sheet 1 if the code matches and the reason code is 2.
I can do the index and match function but am struggling to find a way to only return the result if the reason code is 2.

INDEX(Sheet2!K:K,MATCH($A127,Sheet2!G:G,0),)

Can anyone please advise how to link the result only if the reson code is 2
reason code is in column O on sheet 2

Many thanks

 

[Andrew Poulsom]

Welcome to MrExcel.

In which column is reason code? Can there be more than one matching row?

 

[buxtongt]

Welcome to MrExcel.

Try:

=if(Sheet2!O127="2",INDEX(Sheet2!K:K,MATCH($A127,Sheet2!G:G,0),),"")

I assumed your code you wanted to match was in O127, if not change that to the correct cell. Hope this helps.

 

[Colin39]

Reason code is always in column O but as there are multiple codes and reasons i need to look up against the entire range not just on a single cell
tab 1 will change each day as these are the items that have been shorted
and the reason code will change accordingly so I was thinking of using IF O:O = 2 but that doesnt work

 

[Andrew Poulsom]

Try:

=INDEX(Sheet2!K1:K1000,MATCH(1,INDEX(((Sheet2!G1:G1000=$A127)*(Sheet2!O1:O1000=2)),),0))

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=INDEX(Sheet2!$K$2:$K$2000,MATCH(1,IF(Sheet2!$G$2:$G$2000=$A127,
    IF(Sheet2!$O$2:$O$2000=2,1)),0))

would return the first transaction qualified with reason code of 2.

 

[Colin39]

Great. Many thanks for your assistance