Avatar of sullisnyc44
sullisnyc44
Flag for United States of America asked on

exact match over multiple columns

need a formula that will pull an exact match from multiple columns

Lookup Table
Account ID	Account Name	Original Name
g7pD    	Test1   	Part Name1
g7pD    	Test1   	Part Name2
h8oR    	Test44   	Part Name4
h8oR    	Test44   	Part Name2
t4eY    	Test5   	Part Name3

Open in new window

I need to lookup the case sensitive account ID and the specific part name column header $c$1 in this case across the row of the lookup table
Account ID	Account Name	Part Name2	Exists?
g7p8    	Test1   	3       	??formula here??
t4eY    	Test5   	41      	??formula here??

Open in new window

I already know that I need to use this formula to get the case sensitive match for the acct id
1+MATCH(TRUE,EXACT(OldAcctIDs,A2),0)

but I also need to match across that row the specific 'Part Name2' ($c$1) - part name is not case sensitive
Microsoft ExcelMicrosoft OfficeMicrosoft Applications

Avatar of undefined
Last Comment
byundt

8/22/2022 - Mon
barry houdini

Can you clarify?

Where do the values 3 and 41 come in, shown in your second table - are they to be used in the formula?

You use the header "Exists?" does that mean you just need a Yes/No result......or are you retrieving a specific value.

regards, barry
sullisnyc44

ASKER
yes - I just need to know that it exists in the lookup list.

the 'part name' is a hard coded column header but exists as a value per row in the lookup table.
sullisnyc44

ASKER
I've uploaded a sample.
customSample.xlsx
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
sullisnyc44

ASKER
so ideally I would like to know this:
Account ID      Account Name            Part Name 1      Match?       PartName2      Match?
g7p8                  Fred's Bank                  45            ???                  20            $$$
Mike McCracken

Given your example what should the result be?

mlmcc
SOLUTION
byundt

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sullisnyc44

ASKER
Thanks. If I wanted to add a column next to each part name column that would return the value for that cell?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
sullisnyc44

ASKER
So I think this is working as expected:

={INDEX(QTY,MATCH(1,EXACT(NewAcctIDs,$A2)*($H$1=originaAssetArray),0))}

Where QTY = the quantity column
NewAcctIDs = the account Column
originaAssetArray = original name column

I think I was struggling in the beginning because I was using wider arrays too.

See anything terrible with this?
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.