exact match over multiple columns

sullisnyc44
sullisnyc44 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2013

Commented:
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

Author

Commented:
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.

Author

Commented:
I've uploaded a sample.
customSample.xlsx
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
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 McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Given your example what should the result be?

mlmcc
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Formula returning existence (Yes or No)--must be array-entered:
=IF(ISNA(MATCH(1,EXACT(Items!$B$2:$B$8,A2)*(B2=Items!$C$2:$C$8),0)),"No","Yes")

Formula returning item name of part--must be array-entered:
=IFERROR(INDEX(Items!$D$2:$D$8,MATCH(1,EXACT(Items!$B$2:$B$8,$A2)*($B2=Items!$C$2:$C$8)*(D$1=Items!$E$2:$E$8),0)),"")

Array-entered means:
1) Paste formula in formula bar
2) Hold Control and Shift keys down
3) Hit Enter key
4) Release all three keys.

Excel should respond by adding curly braces { } surrounding your formula. If not, select the cell, click in the formula bar and try again.
customSampleQ-27726062.xlsx

Author

Commented:
Thanks. If I wanted to add a column next to each part name column that would return the value for that cell?
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
If you want to return a value from an adjacent column, you would array-enter:
=IFERROR(INDEX(Items!Adjacent column reference here,MATCH(1,EXACT(Items!$B$2:$B$8,$A2)*($B2=Items!$C$2:$C$8)*(D$1=Items!$E$2:$E$8),0)),"")

Author

Commented:
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?
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
Do you need to worry about error handling? If so, consider wrapping the formula inside an IFERROR function. Otherwise, what you propose seems fine.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial