VLOOKUP - Return Blank

[meppwc]

Trying to get the results that I need

If the value searched on (A2 of "Worksheet A") is found in A1:M30 of "Worksheet B"
Then map the according value that exists in column M (column 13)

But if the cell in column M is blank, map blank instead of "0"

 

[Joe4]

One way:

IF([COLOR=#333333]VLOOKUP(A2,'Worksheet B'!$A$1:$M$12,13,FALSE)=0,"",[/COLOR][COLOR=#333333][FONT=Verdana]VLOOKUP(A2,'Worksheet B'!$A$1:$M$12,13,FALSE))[/FONT][/COLOR]

 

[Eric W]

Are the values in column M numeric or text? If text, try:

=INDEX(Sheet2!$M:$M,AGGREGATE(15,6,ROW(Sheet2!$A$1:$M$30)/(Sheet2!$A$1:$M$30=A2),1))&""

if numeric, let me know and I'll get you a (longer) formula.

 

[meppwc]

I apologize. My boss provided me with incorrect information. Upon meeting with her I was able to get the exact specifications of what she is wanting.

I am trying to create a formula that combines VLOOKUP and SEARCH, and then populates the cell that has the formula with a value only if a certain word exists in an according cell.

The formula will be in cell AA5 of the “MIF” Worksheet.
The lookup_value is in H5 of the “MIF” worksheet.
The table_array is C1:P1172 of the “IB” worksheet.
If a matching value is found, then
On the “IB” worksheet, on the row that the matching value was found on, look at column “P” (text cell). If the word “FAX OPTION” exists anywhere in that text array, populate cell AA5 of the “MIF” Worksheet with “FAX OPTION”.
If “FAX OPTION” does not exist anywhere in the according cell of text, cell AA5 will be blank. (no “0” or “#NA”).

 

[Eric W]

If the lookup value can only occur in column C:

=IF(COUNTIFS(IB!C1:C1172,H5,IB!P1:P1172,"*FAX OPTION*"),"FAX OPTION","")

If it can occur anywhere in C1:P1172:

=IF(SUMPRODUCT((IB!C1:P1172=H5)*ISNUMBER(SEARCH("FAX OPTION",IB!P1:P1172))),"FAX OPTION","")

 

[meppwc]

Thank you very much.............works perfectly

 

[Eric W]

Glad we could help.