Using INDEX & MATCH to VLOOKUP prior column

[TraciAnn via OfficeKB.com]

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?

--

 

[Eduardo]

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

 

[Bassman62]

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]

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.

 

[Harlan Grove]

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.

 

[TraciAnn via OfficeKB.com]

Thanks Harlan!

Got it!

--