# Linking data using IF, INDEX and MATCH

#### Colin39

##### New Member
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),)

reason code is in column O on sheet 2

Many thanks

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Welcome to MrExcel.

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

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.

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

Try:

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

Control+shift+enter, not just enter:
Rich (BB code):
``````=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.

Great. Many thanks for your assistance

Replies
9
Views
272
Replies
7
Views
197
Replies
1
Views
198
Replies
5
Views
197
Replies
1
Views
387

1,203,752
Messages
6,057,153
Members
444,908
Latest member
Jayrey

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back