INDEX and MATCH using two criteria

Hi,

I would like to combine two formula which I've wrote, wondering whether it is possible?

=INDEX([test.xls]Sheet1!$A$2:$A$100,MATCH((--$B2),[test.xls]Sheet1!$B$2:$B$100,FALSE),1

=INDEX([test.xls]Sheet1!$A$4,MATCH($B2&$C2,[test.xls]Sheet1!$B$2&$C$2,0),1)

Note: The second formula isn't working as supoose.

Let me give a background explanation on how I derive fomula no. 1

Logic, I need to match Code on my master reference with those on my working report, if a match is found, it will return Yes under column A 'Exclude' on my working report.

--My working report

Column A
Exclude
(Blank)
(Blank)
(Blank)

Column B
Code
0123456
0123965
9712841

Column C
Currency
S$
S$
S$

--test.xls(My source/master reference)

Column A
Present
Yes
Yes

Column B
Code
9712841
9755041

Column C
Currency
S$
S$

----------------------------------------------------------------------------------------------
Using the formula no.1, i copy and paste to my report, i would get below result after doing an Autofill on column A

Column A
Exclude
(Blank)
(Blank)
Yes
(Blank)

Column B
Code
0123456
0123965
9712841

Column C
Currency
S$
S$
S$

I added the unary -- infront of Cell B2 because working report display 7 digit numbers in General Format whilst my master reference is using Custom format 0000000 make allowance for 7 digits code.


It works well for me until I was told to add in a special criteria, I have an alternative master reference, lets call this 'special reference'

Note: I need to ensure when a match for Code no. 0184342 and Currency : US$ is found, my result will be 'Yes' under Exclude on (Column A)

Below is what I'm hoping to achieve as the end result
----------------------------------------------------------------------------------------------
--Resultant on report should look like this

Column A
Exclude
(Blank)
(Blank)
Yes
(Blank)
Yes
(Blank)

Column B
Code
0123456
0123965
9712841
0184342
0184342

Column C
Currency
S$
S$
S$
S$
US$

--test.xls(My source/master reference)

Column A
Present
Yes
Yes
Yes

Column B
Code
9712841
9755041
0184342

Column C
Currency
S$
S$
US$

I derived formula no.2 hoping to combine it with formula 1 eventually, but SADLY the former doesn't work.

Can someone help pls!! How to combine this two criteria

=INDEX([test.xls]Sheet1!$A$2:$A$100,MATCH((--$B2),[test.xls]Sheet1!$B$2:$B$100,FALSE),1

=INDEX([test.xls]Sheet1!$A$4,MATCH($B2&$C2,[test.xls]Sheet1!$B$2&$C$2,0),1)

Thanks for any advice in advance

Can you uplad example workbook?

Thanks for the solution, i got the gist of it. However, my situation is slightly more complicated.

Can I have you to take a look at this excel file
http://www.megaupload.com/?d=D5QGPTUW

Refering to this excel workbook....
For code no. 0184342, if the currency is US$, i need the result to be "Yes" under Present, else "blank".
Eg. 0184342 under A$, my result will be "blank".

Like wise for code 0349818, if its currency is A$, my result will be "Yes" under Present, else "blank".
Eg 0349818 under HK$, my result will be "blank".

For the rest of the codes, i looking for a match in CODE only.

To summarise, for 0184342 and 0349818, i need the result the be "Yes" under Present if their Currrecny is US$ and A$ respectively.

For the rest of the array, the result will be "Yes" once a match is found in code number, irrespectively of the currency. Eg for code 1728871, once i find a match from my worksheet, i will get a Yes indicator at the Present column, irrregardless of what the currency.

Below is my formula which doesn't need the criteria I was hoping for.

Entering this on cell A2 of my worksheet
=IF(ISNA(INDEX([test.xls]Sheet1!$A$2:$A$492,MATCH($B2&"|"&$D2,[test.xls]Sheet1!$E$2:$E$492,FALSE))),"",INDEX([test.xls]Sheet1!$A$2:$A$492,MATCH($B2&"|"&$D2,[test.xls]Sheet1!$E$2:$E$492,FALSE)))

Is excel formula applicable in this case? I will base my findings on the data value returned at column A of the worksheet.

Thanks in advance for any advice

Originally Posted by DonkeyOte

If you have a file please upload here directly - if too big trim accordingly
(it is unlikely we need to see everything in order to get the gist of what you want)

Sorry I wasn't aware that there's an option for uploading attachment. My bad.

In this case let me know if you have a solution to my post. Thank you so much!!