Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
Hello!
I use range names for all my functions and I'm trying to lookup a number (LeadID) in a column (TechUserID) on another sheet (same workbook) and return the value one column to the left (TechFullName). I am using: =INDEX(TechFullName,MATCH(LeadID,TechUserID,0),1) But it is returning an "#N/A" The sort order of the sheet varies so I'm not sure if that has an impact in '07 like it did in previous versions, but I know it doesn't matter with VLOOKUP any more. Am I using the right function? -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
Hi,
I assume that TechUser ID in in sheet2 column B, and you want the result from column A LeadID is in sheet 1 in column A starting row 1 In cell B1 sheet 1 enter =sumproduct(--(A1=Sheet2!$B$1:$B$100),sheet2!$A$1:$A$100) Change sheet name and range to fit your needs but remember that the range has to be the same in both parts of the formula "TraciAnn via OfficeKB.com" wrote: Hello! I use range names for all my functions and I'm trying to lookup a number (LeadID) in a column (TechUserID) on another sheet (same workbook) and return the value one column to the left (TechFullName). I am using: =INDEX(TechFullName,MATCH(LeadID,TechUserID,0),1) But it is returning an "#N/A" The sort order of the sheet varies so I'm not sure if that has an impact in '07 like it did in previous versions, but I know it doesn't matter with VLOOKUP any more. Am I using the right function? -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
TraciAnn
Using your explanation, your formula work fine for me (xl2007). The sort order did not seem to affect the result. I was able to return "#N/A" if LeadID did not find a match within TechUserID. Make sure the data types for LeadID and TechUserID are the same. Is one being entered as text and the other numeric? Hope this helps. "TraciAnn via OfficeKB.com" <u50702@uwe wrote in message news:961313837edda@uwe... Hello! I use range names for all my functions and I'm trying to lookup a number (LeadID) in a column (TechUserID) on another sheet (same workbook) and return the value one column to the left (TechFullName). I am using: =INDEX(TechFullName,MATCH(LeadID,TechUserID,0),1) But it is returning an "#N/A" The sort order of the sheet varies so I'm not sure if that has an impact in '07 like it did in previous versions, but I know it doesn't matter with VLOOKUP any more. Am I using the right function? -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
Thanks Eduardo.
The problem was that some of the originating data needed cleaned of extrenuous spaces. I wasn't expecting that because of the data source. My initial INDEX function worked perfectly. Thanks again. -- --- TraciAnn Message posted via http://www.officekb.com |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
"TraciAnn via OfficeKB.com" <u50702@uwe wrote...
I use range names for all my functions and I'm trying to lookup a number (LeadID) in a column (TechUserID) on another sheet (same workbook) and return the value one column to the left (TechFullName). I am using: =INDEX(TechFullName,MATCH(LeadID,TechUserID,0), 1) But it is returning an "#N/A" .... The INDEX call certainly isn't the problem. The named range TechFullName could only be the problem if there were #N/A values in it on the same row as the MATCH call returns: if that were the case, your formula would be returning the correct result. So the likely problem is the MATCH call. It's possible (even likely) that either TechUserID has a mixture of text and numeric values or LeadID is a different type than the seemingly matching cell in TechUserID. The expedient solution would be to try the array formula =INDEX(TechFullName,MATCH(--LeadID,--TechUserID,0),1) which forces both LeadID and TechUserID to be converted to numeric values and performs numeric comparisons. If that also produces an #N/A error, then you need to check the actual contents of both ranges. It's possible various cells count contain trailing HTML nonbreaking spaces (decimal character code 160). If so, you'd need to use a formula like =SUBSTITUTE(x,CHAR(160),"") in a different column to strip off those characters, then copy that range of formulas and paste special as values onto the original TechUserID column to replace those values with cleansed values. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using INDEX & MATCH to VLOOKUP prior column
Thanks Harlan!
Got it! -- --- TraciAnn Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...tions/200905/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/Match or Vlookup | Excel Worksheet Functions | |||
Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
Vlookup or Index/Match | Excel Discussion (Misc queries) | |||
Vlookup, Index & Match | Excel Worksheet Functions |