• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

match with dual search criteria

M

monoj chakraborty

Member
I am working with two different worksheets and trying to use index and match functions.

Worksheet 1 : has a set of codes
worksheet 2 : has a set of codes but also a set of customer names

I want to match the codes in worksheet1 against those of worksheet 2 but only if the customer name matches that of a particular name (let's call him Mclane)

my formula looks like this :

=+INDEX('https://genmills-my.sharepoint.com/Users/G560083/OneDrive - General Mills/MIS/MIS/[USRO and other dump.xlsx]USRO'!$E$2:$G$22622,MATCH(D11&"mclane global",('https://genmills-my.sharepoint.com/Users/G560083/OneDrive - General Mills/MIS/MIS/[USRO and other dump.xlsx]USRO'!$G$2:$G$22622)&('https://genmills-my.sharepoint.com/Users/G560083/OneDrive - General Mills/MIS/MIS/[USRO and other dump.xlsx]USRO'!$E$2:$E$22622),0),3)



but this one does not seem to be working as its returning n/a when I know for sure that some codes should match between the two sheets

Thanks[/code]
 

Attachments

  • upload_2017-12-9_9-23-58.png
    upload_2017-12-9_9-23-58.png
    22.5 KB · Views: 4
Last edited:
Hi ,

If we remove the path name and the file name from your formula , it is basically this :

=INDEX(USRO!$G$2:$G$22622,MATCH(D11 & "mclane global",USRO!$G$2:$G$22622 & USRO!$E$2:$E$22622,0))

Note that because of the concatenation involved in the MATCH function , the formula has to be entered as an array formula , using CTRL SHIFT ENTER.

I entered a code in D11 , and then , in the USRO tab entered the code and the customer name , and the formula worked correctly.

Narayan
 
thanks Narayan - i did enter it as an array formula. it works now thanks.

Is it also possible to rewrite the formula like this :

{+index(usro!e2:g22000,match(d11, usro!g2:g22000&usro!e2:e22000="maclane global",0),3)}
 
monoj chakraborty said:
thanks Narayan - i did enter it as an array formula. it works now thanks.

Is it also possible to rewrite the formula like this :

{+index(usro!e2:g22000,match(d11, usro!g2:g22000&usro!e2:e22000="maclane global",0),3)}
Click to expand...
Hi ,

No. The version you have posted needs to be changed slightly for it to work.

Try this array formula , to be entered using CTRL SHIFT ENTER :

=INDEX(USRO!$G$2:$G$22,MATCH(D11, USRO!$G$2:$G$22 * (USRO!$E$2:$E$22 = "mclane global"),0))

The multiplication symbol has to be used , since the expression (USRO!$E$2:$E$22 = "mclane global") will generate an array of TRUE / FALSE values , which when the multiplication operation is performed will become 1 / 0 values. Multiplying this by the code will return the code itself.

Note that the above will work only if the code is numeric ; if the code is alphanumeric , then an IF statement will have to be used instead of the multiplication operator , as follows :

=INDEX(USRO!$G$2:$G$22,MATCH(D11, IF(USRO!$E$2:$E$22 = "mclane global", USRO!$G$2:$G$22),0))

Narayan
 
Hi,

I wrote the * formula as above (check the screen shot below) :


upload_2017-12-11_19-42-25.png

the formula seems to be returning just one value from the top value of the source data and that too from rows, which dont have data in search column of the destination file.

I also checked whether the code is numeric or alphanumeric and it does seem to be numeric alright.

Thanks
 
You must log in or register to reply here.
Back
Top