Match multiple fields

[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.

 

[John C]

=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.

 

[Belinda7237]

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?

 

[ShaneDevenshire]

Hi,

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

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

Array entered.

 

[Belinda7237]

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

 

[John C]

You are welcome, and thanks for the feedback