Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Match multiple fields

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.





  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Match multiple fields

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


"Belinda7237" wrote:

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.





  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Match multiple fields

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?

"John C" wrote:

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


"Belinda7237" wrote:

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.





  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Match multiple fields

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,
Shane Devenshire


"Belinda7237" wrote:

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.





  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 106
Default Match multiple fields

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

"John C" wrote:

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


"Belinda7237" wrote:

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.







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,358
Default Match multiple fields

You are welcome, and thanks for the feedback :)
--
John C


"Belinda7237" wrote:

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

"John C" wrote:

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


"Belinda7237" wrote:

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.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Match Corresponding Rows and Add Fields Together Dan Excel Worksheet Functions 2 April 2nd 08 02:45 PM
Trying to match certain fields from 1 workbook to another AngelaD Excel Discussion (Misc queries) 0 January 15th 07 09:02 PM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 0 November 11th 04 01:16 AM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 1 November 10th 04 11:26 PM
Duplicate fields does not match up! If statement Patsy Excel Worksheet Functions 0 November 3rd 04 04:53 PM


All times are GMT +1. The time now is 06:28 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"