Link to home
Create AccountLog in
Avatar of sullisnyc44
sullisnyc44Flag 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
Avatar of barry houdini
barry houdini
Flag of United Kingdom of Great Britain and Northern Ireland image

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
Avatar of 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.
I've uploaded a sample.
customSample.xlsx
so ideally I would like to know this:
Account ID      Account Name            Part Name 1      Match?       PartName2      Match?
g7p8                  Fred's Bank                  45            ???                  20            $$$
Avatar of Mike McCracken
Mike McCracken

Given your example what should the result be?

mlmcc
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks. If I wanted to add a column next to each part name column that would return the value for that cell?
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
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
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account