| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
match formula question
I am trying to match data from two seperate spreadsheets to return a value
but the formatting of the data i need to match is not the same: the id field in one sheet is: 0000100011634180000000265 and the data in the other set is: 01-1163418-265 these are actually the same and i would want to return the value in column N. I was trying to use this: =IF(ISNA(MATCH(H6,'Drop last comp report here'!$H:$H,0)),"",INDEX('Drop last comp report here'!N:N,MATCH(H6,'Drop last comp report here'!H:$H,0),1)) but how do i acct for formatting difference? Thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match formula question
I think I got all the ranges correct; give this formula a try...
=IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0))) Rick "Belinda7237" wrote in message ... I am trying to match data from two seperate spreadsheets to return a value but the formatting of the data i need to match is not the same: the id field in one sheet is: 0000100011634180000000265 and the data in the other set is: 01-1163418-265 these are actually the same and i would want to return the value in column N. I was trying to use this: =IF(ISNA(MATCH(H6,'Drop last comp report here'!$H:$H,0)),"",INDEX('Drop last comp report here'!N:N,MATCH(H6,'Drop last comp report here'!H:$H,0),1)) but how do i acct for formatting difference? Thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match formula question
Here is the same formula, but deliberately broken apart so that your
newsreader won't split the text at the blank spaces... =IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)& "-"&--RIGHT(H6,10),$B:$B,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT( H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),$B:$B,0))) Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think I got all the ranges correct; give this formula a try... =IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0))) Rick "Belinda7237" wrote in message ... I am trying to match data from two seperate spreadsheets to return a value but the formatting of the data i need to match is not the same: the id field in one sheet is: 0000100011634180000000265 and the data in the other set is: 01-1163418-265 these are actually the same and i would want to return the value in column N. I was trying to use this: =IF(ISNA(MATCH(H6,'Drop last comp report here'!$H:$H,0)),"",INDEX('Drop last comp report here'!N:N,MATCH(H6,'Drop last comp report here'!H:$H,0),1)) but how do i acct for formatting difference? Thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
match formula question
thanks, it didnt work at first but i understood the concept yo uwere using
and figured out that i just needed to add the location of the N:N column tab name - then it worked - thanks - what a powerful formula! "Rick Rothstein (MVP - VB)" wrote: Here is the same formula, but deliberately broken apart so that your newsreader won't split the text at the blank spaces... =IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)& "-"&--RIGHT(H6,10),$B:$B,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT( H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),$B:$B,0))) Rick "Rick Rothstein (MVP - VB)" wrote in message ... I think I got all the ranges correct; give this formula a try... =IF(ISNA(MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0)),"",INDEX(N:N,MATCH(TEXT(--LEFT(H6,5),"00")&"-"&--MID(H6,6,10)&"-"&--RIGHT(H6,10),'Drop last comp report here'!$H:$H,0))) Rick "Belinda7237" wrote in message ... I am trying to match data from two seperate spreadsheets to return a value but the formatting of the data i need to match is not the same: the id field in one sheet is: 0000100011634180000000265 and the data in the other set is: 01-1163418-265 these are actually the same and i would want to return the value in column N. I was trying to use this: =IF(ISNA(MATCH(H6,'Drop last comp report here'!$H:$H,0)),"",INDEX('Drop last comp report here'!N:N,MATCH(H6,'Drop last comp report here'!H:$H,0),1)) but how do i acct for formatting difference? Thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Match formula question | Excel Worksheet Functions | |||
| Match Question | Excel Worksheet Functions | |||
| INDEX/MATCH formula question | Excel Discussion (Misc queries) | |||
| Question about the "Match" formula | Excel Discussion (Misc queries) | |||
| How do I display more than one match in a Index/Match formula? | Excel Worksheet Functions | |||
Hybrid Mode
