Match multiple fields

  • Thread starter Thread starter Belinda7237
  • Start date Start date
B

Belinda7237

I cannot quite get this right - Hoping someone can help

Column A Column B Column C Column D
53 126651 265 99

I have a sheet set up like above and then I have another sheet with the
values of
column A, B and c combined.

Column A in worksheet 2

000530001266510000000265

I want to be able to match workseet 2 with the three columns in worksheet 1
to return column D in worksheet 1.
 
=SUMPRODUCT(--(Sheet1!$A$1:$A$100=--LEFT(A2,5)),--(Sheet1!$B$1:$B$100=--MID(A2,6,9)),--(Sheet1!$C$1:$C$100=--RIGHT(A2,10)),(Sheet1!$D$1:$D$100))

This assumes that the text in column A of sheet 2 is always:
First 5 characters for column A, next 9 characters for column B, and final
10 characters for column C. If the column A sheet 2 has varying lengths
(either overall, or for each column) please post back and give more detail as
to how that column is configured.

Hope this helps.
 
thanks, there are always fixed characters so i see the way you are writing
this, however, it returns 0 in each field when in reality there should be a
result - i checked my formatting and each field is set to text. would the
fact that the zeros are dropped have anything to do with it not returning a
value?
 
Hi,

In a perfect world you data might contain no "0"'s in the four cells, in
which case you could use

=INDEX(D1:D4,MATCH(SUBSTITUTE(Sheet2!B8,"0",""),A1:A4&B1:B4&C1:C4,0))

Array entered.
 
thanks, i figured it out - my mid i needed to do A2,6,10) and it worked -
thanks so much!
 
Back
Top