VLOOKUP + IF + AND help required please.

[tammorice]

Hi everyone. I have found myself in a spiral of inadequate formulas. I do hope someone can help me out. So this is what we have.

ID numbers in file are reconciled with a download of ID numbers from the Regulator website. I want to have an entry populated in the 'Validity' column after matching the ID numbers and then confirming the status as OK (if GENR) and INVALID (if 'Not valid).

If an ID number listed (on file) is not in the ID number download column from the Regulator, the ID number in the 'on file' column is highlighted.

There will be multiple copies of ID numbers listed against jobs in the file column, and only a single ID number in the download column.

Thanks to everyone who reads and provides comments/feedback.

ID number - on file	ID number - download (status/validity)	Status - download (status/validity)	Validity - file reconciliation
12690068	10146637	GENR	[OK / INVALID]
11596470	10686175	GENR	[OK / INVALID]
80792548	11596470	Not valid	[OK / INVALID]

 

[ER_Neha]

Hello Please try this, I hope it helps

Book1
	A	B	C	D
1	ID number - on file	ID number - download (status/validity)	Status - download (status/validity)	Validity - file reconciliation
2	12690068	10146637	GNR	OK
3	11596470	10686175	GNR	OK
4	80792548	11596470	Not Valid	INVALID
Sheet1

Cell Formulas
Range	Formula
C2:C4	C2	=IF(ISNUMBER(VLOOKUP(B2,$A$2:$A$4,1)),"Not Valid","GNR")
D2:D4	D2	=IFS(C2="GNR","OK",C2="Not Valid","INVALID")

 

[tammorice]

Thank you for the quick assistance. I didn't explain myself very well, so I thought I'd provide a little more info on what I need and an extract of the file

Background

• I am required to check that each of the employees have a valid ID before performing the assigned job. (note that an employee may have more than one job for the day - column A has 200 lines).
• I upload a csv of employee ID numbers (column D 162 lines) to the regulators website.
• The Regulator then provides a file containing columns D and E with E being the status.
• Depending on the status in column E, a response is generated in column C (Approved) of "OK" or "INVALID"
• My formula doesn't account for the employee ID existing but being 'not current' and therefore employee cannot perform work that day.

So Column C = the result of answering 2 questions.

1. is the employee ID in column A also in column D;
2. is the status of the employee ID in column A 'not current'

Answers:

• Query 1 above is true(then lookup status),
  • status is 'not current' (then return "INVALID in column C - next to job No.), OR
  • status is any value other than 'not current' (then return "OK" in column C - next to job No.)
• Query 1 above is false (then return "INVALID" in column C - next to job No.)

i.e. in the example below C15 should = INVALID (and against all other jobs for that employee ID value in column A)

VLOOKUP QUESTION.xlsx
	A	B	C	D	E
1	Employee ID No. (file)	Job No.	Approved	Employee ID No. (download)	ID Status (download)
2	12690068	98966	OK	10146637	GENR
3	30550019	98967	OK	10686175	GENRSCHE
4	27317043	98970	OK	11596470	GENRSCHE
5	102162223	98971	OK	11610117	GENR
6	23776784	98972	OK	12500988	GENRSCHE
7	122226504	98973	OK	12661274	GENR
8	85669073	98974	OK	12690068	GENR
9	16616668	98975	OK	12856812	GENRSCHE
10	125862531	98977	OK	13108731	BHTXGENRSCHE
11	107452690	98976	OK	13203489	GENRSCHE
12	14323197	98979	OK	13229285	GENRSCHE
13	32250090	98978	OK	13234585	GENR
14	19585099	98980	OK	13235289	GENR
15	15096131	98981	OK	14323197	Not current
16	22717845	98983	OK	14755986	GENRSCHE
17	38287521	98982	OK	15004509	GENRSCHE
18	27317043	98984	OK	15069563	GENR
19	29455600	98985	OK	15096131	GENRSCHE
20	40605546	98987	OK	15280216	GENRSCHE
21	10686175	98986	OK	16616668	BHTXLIMOGENRSCHE
Sheet1

Cell Formulas
Range	Formula
C2:C21	C2	=IF(VLOOKUP(A2,$D$2:$D$162,1,FALSE),"OK","INVALID")

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
E:E	Cell Value	contains "not current"	text	NO

 

[tammorice]

Hey guys . . .. I did it! I worked out a formula using INDEX/MATCH. . .