SOLVED

help determining proper function to use

%3CLINGO-SUB%20id%3D%22lingo-sub-2851320%22%20slang%3D%22en-US%22%3Ehelp%20determining%20proper%20function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2851320%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20help%20determining%20the%20proper%20function%20if%20one%20exists.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20comparing%202%20different%20cells%20to%20determine%20a%20number%20to%20place%20in%20another%20cell.%3C%2FP%3E%3CP%3EThe%20basis%20is%3A%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20c2%3D%22NIL%22%20and%20d2%3D%22ASCUS%22%20then%20%221%2F2%22%20should%20be%20in%20e2%26nbsp%3B%20OR%3C%2FP%3E%3CP%3EIF%20c2%3D%22NIL%22%20and%20d2%3D%22LSIL%22%20then%20%221%22%20should%20be%20in%20e2%26nbsp%3B%20OR%26nbsp%3B%3C%2FP%3E%3CP%3EIF%20c2%3D%22NIL%22%20and%20d2%3D%22HSIL%22%20then%20%222%22%20should%20be%20in%20e2.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThere%20are%20multiple%20other%20comparison%20I%20want%20to%20make%20also.%20So%20maybe%206%20different%20comparisons%20in%20e2.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2851320%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2851376%22%20slang%3D%22en-US%22%3ERe%3A%20help%20determining%20proper%20function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2851376%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1186927%22%20target%3D%22_blank%22%3E%40LUKEHARWELL22%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI'd%20create%20a%20small%20lookup%20table.%20In%20the%20screenshot%20below%2C%20it%20is%20in%20H2%3AI7.%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20E2%20is%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(C2%3D%22NIL%22%2CVLOOKUP(D2%2C%24H%242%3A%24I%247%2C2%2CFALSE)%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0822.png%22%20style%3D%22width%3A%20603px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317789i2B6A0FD7DE128163%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0822.png%22%20alt%3D%22S0822.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2851493%22%20slang%3D%22en-US%22%3ERe%3A%20help%20determining%20proper%20function%20to%20use%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2851493%22%20slang%3D%22en-US%22%3EHey%20thanks%20for%20that.%20That%20will%20probably%20be%20my%20best%20bet%20going%20forward.%20Now%20to%20add%20other%20scenarios.%20with%20c2.%3CBR%20%2F%3E%3CBR%20%2F%3Eif%20c2%3Dascus%20and%20d2%3Dnil%20then%20e2%3D%221%2F2%20or%3CBR%20%2F%3Eif%20c2%3Dlsil%20and%20d2%3Dnil%20then%20e2%3D1%20or%3CBR%20%2F%3Ec2%3Dhsil%20and%20d2%3Dascus%20then%20e2%3D2.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20I%20do%20vlookup%20with%20c2%20being%20multiple%20different%20things%3F%3CBR%20%2F%3Ethanks.%3C%2FLINGO-BODY%3E
New Contributor

I need help determining the proper function if one exists.

 

I am comparing 2 different cells to determine a number to place in another cell.

The basis is: 

IF c2="NIL" and d2="ASCUS" then "1/2" should be in e2  OR

IF c2="NIL" and d2="LSIL" then "1" should be in e2  OR 

IF c2="NIL" and d2="HSIL" then "2" should be in e2. 

 

There are multiple other comparison I want to make also. So maybe 6 different comparisons in e2. 

 

Thanks. 

3 Replies

@LUKEHARWELL22 

I'd create a small lookup table. In the screenshot below, it is in H2:I7.

The formula in E2 is

=IF(C2="NIL",VLOOKUP(D2,$H$2:$I$7,2,FALSE),"")

S0822.png

Hey thanks for that. That will probably be my best bet going forward. Now to add other scenarios. with c2.

if c2=ascus and d2=nil then e2="1/2 or
if c2=lsil and d2=nil then e2=1 or
c2=hsil and d2=ascus then e2=2.

How can I do vlookup with c2 being multiple different things?
thanks.
best response confirmed by LUKEHARWELL22 (New Contributor)
Solution

@LUKEHARWELL22 

I'd use a two-dimensional lookup table:

S0823.png

See the attached sample workbook.