What formula do i need vlookup to index match or something else?

[Dazzybeeguy]

I need a formula that if cell c4 is populated than it looks up the value in Cell C4 in worksheet 2 (called Data2) say column A:A and I want it to give the value in worksheet 2 column M:M, if the value is not in the Data2 worksheet I want it to return "Ref Not found"

So a lookup works fine, however if the value in C4 is not there it gives #N/A

So both of the formulas I tried give the #N/A result if there is a value in C4 but it is not in the Data2 worksheet =IF(C4>0,VLOOKUP(D4,Data2!A2:N9999,14,FALSE),"")

I tried =INDEX(Data2!M:M,MATCH(D4,Data2!A:A,0))&" Last Comment - "&INDEX(Data2!N:N,MATCH(D4,Data2!A:A,0)) but still get #N/A if the value in C4 is not in the other worksheet

Thanks

 

[Asbestos_Jen]

Both VLOOKUP and INDEX should result in #N/A if the target is not found. If you want it to return something else, wrap your lookup (or index,match,match) in IFERROR(). For example, assuming your concatenated INDEX formulas work when the target value is found:

=IFERROR(INDEX(Data2!M:M,MATCH(D4,Data2!A:A,0))&" Last Comment - "&INDEX(Data2!N:N,MATCH(D4,Data2!A:A,0)),"Ref Not found")

 

[kevin9999]

Your post is a bit confusing, are you trying to find the value in C4 or D4?

=IF(C4>0,VLOOKUP(D4,Data2!A2:N9999,14,FALSE),"")

=INDEX(Data2!M:M,MATCH(D4,Data2!A:A,0))&" Last Comment - "&INDEX(Data2!N:N,MATCH(D4,Data2!A:A,0)) but still get #N/A if the value in C4 is not in the other worksheet

 

[Peter_SSs]

If you are using your 365 version of Excel rather than the 2010 you can use the more specific IFNA function rather than the more general IFERROR function (which may hide some other errors that you might want to know about). You could also save having to to MATCH D4 in column A twice & just do it once.

=LET(m,MATCH(D4,Data2!A:A,0),IFNA(INDEX(Data2!M:M,m)&" Last Comment - "&INDEX(Data2!N:N,m),"Ref not found"))

 

[Dazzybeeguy]

Your post is a bit confusing, are you trying to find the value in C4 or D4?

D4 trims the data in c4 just in case an extra space is added

 

[Dazzybeeguy]

Both VLOOKUP and INDEX should result in #N/A if the target is not found. If you want it to return something else, wrap your lookup (or index,match,match) in IFERROR(). For example, assuming your concatenated INDEX formulas work when the target value is found:

=IFERROR(INDEX(Data2!M:M,MATCH(D4,Data2!A:A,0))&" Last Comment - "&INDEX(Data2!N:N,MATCH(D4,Data2!A:A,0)),"Ref Not found")

The only issue is if C4 is empty it returns the "Ref not found" when I want it to return no value. I can use the formula in say cell A1 then use formula =IF(C4>0,A1,"")

 

[Dazzybeeguy]

If you are using your 365 version of Excel rather than the 2010 you can use the more specific IFNA function rather than the more general IFERROR function (which may hide some other errors that you might want to know about). You could also save having to to MATCH D4 in column A twice & just do it once.

=LET(m,MATCH(D4,Data2!A:A,0),IFNA(INDEX(Data2!M:M,m)&" Last Comment - "&INDEX(Data2!N:N,m),"Ref not found"))

The only issue is if C4 / D4 is empty it returns the "Ref not found" when I want it to return no value. I can use the formula in say cell A1 then use formula =IF(C4>0,A1,"")

 

[kevin9999]

I think I follow you. Please give this a try:

=IF(D4="","",IFERROR(VLOOKUP(D4,Data2!A:N,14,FALSE),"Ref Not Found"))

 

[Peter_SSs]

The only issue is if C4 / D4 is empty ..

One of your post #1 formulas had =IF(C4>0,... but the other didn't, so a bit hard to know exactly what you did want. Still guessing but could it be this?

=IF(AND(C4>0,D4<>""),LET(m,MATCH(D4,Data2!A:A,0),IFNA(INDEX(Data2!M:M,m)&" Last Comment - "&INDEX(Data2!N:N,m),"Ref not found")),"")