VLOOKUP return for three conditions?

[kznmrexcel]

I need a formula that will return one of three things:
1. If the Comments column (column B on cbBarcodeScans sheet) has the letter code g, return "Device issued with Gumdrop case." (No underline)
2. If that same Comments column (column B on cbBarcodeScans sheet) is blank, return blank. (Not the text "blank," but return a blank cell.)
3. If the number we're looking up from cell R2 is not present on cbBarcodeScans sheet in column A, return "Not scanned."

This is where I started, but I need help putting in the conditions:
=VLOOKUP(R2,cbBarcodeScans!$A:$B,2,FALSE)

TIA

 

[awoohaw]

Not going to recreate your scenario exactly, but try this:

data (4).xlsx
	A	B	C	D	E	F	G
2
3	NumberLookup	Comments				R2 Value	Formula Result
4	1					9	Not Scanned
5	2					7
6	3					6
7	4	g				8	Not Scanned
8	5		blank B			1
9	6					2
10	7					4	Device Issued with Gumdrop Case
11						3
12						5
13						10	Not Scanned
kznmrexcel

Cell Formulas
Range	Formula
G4:G13	G4	=IF(ISNA(VLOOKUP($F4,$A$4:$B$13,2,0)),"Not Scanned", IF(VLOOKUP($F4,$A$4:$B$13,2,0)="g","Device Issued with Gumdrop Case", IF(VLOOKUP($F4,$A$4:$B$13,2,0)="","",2)))

 

[Peter_SSs]

Another option to test.

23 06 03.xlsm
	A	B
1	NumberLookup	Comments
2	1
3	2
4	3
5	4	g
6	5
7	6	g
8	7
cbBarcodeScans

23 06 03.xlsm
	R	S
1
2	9	Not scanned
3	7
4	6	Device issued with Gumdrop case
5	8	Not scanned
6	1
7	2
8	4	Device issued with Gumdrop case
9	3
10	5
11	10	Not scanned
3 Choices

Cell Formulas
Range	Formula
S2:S11	S2	=IFNA(IF(VLOOKUP(R2,cbBarcodeScans!A:B,2,0)="","","Device issued with Gumdrop case"),"Not scanned")

 

[DanteAmor]

Hi @kznmrexcel.

1. If the Comments column (column B on cbBarcodeScans sheet) has the letter code g

If it's the letter "g" you're looking for, then it could be:

Dante Amor
	A																	R	S
1
2																		9	Not scanned
3																		7
4																		6	Device issued with Gumdrop case
5																		8	Not scanned
6																		1
7																		2
8																		4	Device issued with Gumdrop case
9																		3
10																		5
11																		10	Not scanned
Sheet

Cell Formulas
Range	Formula
S2:S11	S2	=IFERROR(IF(VLOOKUP(R2,cbBarcodeScans!A:B,2,0)="g","Device issued with Gumdrop case",""),"Not scanned")

Data in cbBarcodeScans sheet:

Dante Amor
	A	B
1	NumberLookup	Comments
2	1
3	2
4	3
5	4	g
6	5
7	6	g
8	7
cbBarcodeScans