N/A Error Using VLOOKUP even though there is match?

[chicity26]

Hi guys,

Could someone look at my workbook and explain why I am getting N/A errors in sheet 1? Also, how can I fix this problem?

For example, in sheet1, Cell B2 should equal 3. And it should stretch across the entire data range, so even something like B14 should return 3.

Thanks.

[FDibbins]

You cannot use VLOOKUP like that. The criteria column must be the 1st column ib the range, vlookup cannot (generally) look "backawrds".

Try this instead...
=IFERROR(INDEX(Sheet2!$A$2:$A$13,MATCH(Sheet1!A2,Sheet2!$B$2:$B$13,0)),"")
copied down

The IFERROR is to error-trap for those critera for which there is no match

[vogel997]

try this

Project ID
100 3 =SUMPRODUCT(--(A2=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
200 5 =SUMPRODUCT(--(A3=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
300 7 =SUMPRODUCT(--(A4=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
400 4 =SUMPRODUCT(--(A5=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
500 8 =SUMPRODUCT(--(A6=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
500 8 =SUMPRODUCT(--(A7=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
600 9 =SUMPRODUCT(--(A8=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
600 9 =SUMPRODUCT(--(A9=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
700 1 =SUMPRODUCT(--(A10=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
700 1 =SUMPRODUCT(--(A11=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
700 1 =SUMPRODUCT(--(A12=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
15000 2 =SUMPRODUCT(--(A13=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)
26 3 =SUMPRODUCT(--(A14=Sheet2!$B$2:$D$13)*Sheet2!$A$2:$A$13)

[chicity26]

What if I want it also to match from columns C and D in sheet 2? For example if the value is Project = 600, this should get ID = 9.

[chicity26]

vogel997, the actual set I am working with has hundreds of thousands of rows. Excel might not have the resources for that.

[vogel997]

vogel997, the actual set I am working with has hundreds of thousands of rows. Excel might not have the resources for that.

Then try this

=INDEX(Sheet2!A:A,IFERROR(MATCH(A2,Sheet2!$B:$B,0),IFERROR(MATCH(A2,Sheet2!C:C,0),MATCH(A2,Sheet2!D:D,0))),)

[Mazzaropi]

chicity26, Good evening.

I did an example for you:
24-07-2014_ExcelForum_Book1-OK.xlsx

Is it what you want?
I hope it helps.

[FDibbins]

Try this...
=INDEX(Sheet2!$A$2:$A$13,IFERROR(MATCH(Sheet1!A2,Sheet2!$B$2:$B$13,0),IFERROR(MATCH(Sheet1!A2,Sheet2!$C$2:$C$13,0),MATCH(Sheet1!A2,Sheet2!$D$2:$D$13,0))))

[chicity26]

FDibbins and vogel997, those formulas work well for the smaller set. But the actual set also has nearly 60 columns for Project categories. Inputting that into one function would be too much, I think.