Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Corresponding Rows and Add Fields Together | Excel Worksheet Functions | |||
Trying to match certain fields from 1 workbook to another | Excel Discussion (Misc queries) | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions | |||
Duplicate fields does not match up! If statement | Excel Worksheet Functions |