VLookup returns the result from the row above

[DEEJ3028]

Hi, this is my first post, so take it easy on me.
I have a VLookup formula which is standard with the table array on one sheet and the lookup data in another sheet.
The formula insists on returning the value from the row above the correct result, I hope this makes sense.
So 2 sheets, first sheet is CourseList where the table information is contained in B$2:G$5828 where the lookup column is B and the result is in G
2nd Sheet is where I have the Vlookup function where column A contains the lookup value.
My formulae is =VLOOKUP(A2,CourseList!B$2:G$5830,6,1)
The correct data is in G2910 but the lookup returns data from G2909. There is not a close match to the lookup value other than the correct data in G2910.
Any help would be appreciated

 

[Z51]

Try changing the last value in the formula from 1 to 0. 0 = exact match lookup, while 1 is not.

 

[Peter_SSs]

Welcome to the MrExcel board!

It really would help if we could see at least a small section of CourseList columns B:G so that we can see whether the data is numeric or not, whether it is sorted or not etc.
Such a sample with XL2BB would be great and you can hide columns C:F before creating the Mini Sheet.
You would also need to disguise any sensitive data in columns B & G.
Then also tell us a relevant lookup value and what result you expect from that lookup value.

 

[DEEJ3028]

I have downloaded the addin and installed it. This is the result of the 2 sheets involved

Example of VLookup.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	CourseName			Tags	Course Start Date	StartDate	Learning Activities	Course Completed	Certificate	Date of certificate	CompletionDate	Score of certificate	Video watched	Total video duration	Time spent in course	Average Course Score	Vendor
2	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-10-24 11:35:26	44858	2 / 2	✓	✓	2022-10-24 18:01:45	44858		00:00:00	00:00:00	00:40:37		Royal College of Radiologists
3	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-11-23 10:24:18	44888	2 / 2	✓	✓	2022-11-23 10:27:37	44888		00:00:00	00:00:00	00:03:11		Royal College of Radiologists
4	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-11-21 11:42:36	44886	2 / 2	✓	✓	2022-11-21 11:47:44	44886		00:00:00	00:00:00	00:04:36		Royal College of Radiologists
5	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-11-24 13:08:42	44889	2 / 2	✓	✓	2022-11-24 13:11:03	44889		00:00:00	00:00:00	00:02:03		Royal College of Radiologists
6	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-10-24 12:20:42	44858	0 / 2				#VALUE!		00:00:00	00:00:00	00:00:00		Royal College of Radiologists
7	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-10-24 12:39:35	44858	2 / 2	✓	✓	2022-10-28 14:13:15	44862		00:00:00	00:00:00	00:00:26		Royal College of Radiologists
8	In-Depth Knowledge of Analge			cf_workingrole_ambulance	2022-11-24 16:05:43	44889	2 / 2	✓	✓	2022-11-24 16:07:59	44889		00:00:00	00:00:00	00:02:07		Royal College of Radiologists
In-Depth Knowledge of Analge

Cell Formulas
Range	Formula
F2:F8,K2:K8	F2	=DATEVALUE(MID(E2,9,2)&"/"&MID(E2,6,2)&"/"&LEFT(E2,4))
Q2:Q8	Q2	=VLOOKUP($A2,CourseList!B$2:G$23,6,1)

and

Example of VLookup.xlsx
	A	B	C	D	E	F	G
1	CourseName	Name	Category	Type	StartDate	External Id	Vendor
2	e8e7040a-1df1-44e2-82c5-ae1e889dfb69	Preformed Dental Implants	Dentistry	Online Course	Active	HEE_DEN_08_056	Faculty of Dental Surgery Royal College of Surgeons England
3	c096dc37-3e31-4700-af9c-7451c3e959c7	Impression Taking and Contraindications	Audiology	Online Course	Active	HEE_AUD_1_07_06	NHS Health Education England
4	952da2f0-7ae1-4afc-8197-457d3209d3cf	Impression Techniques	Dentistry	Online Course	Active	HEE_DEN_09_023	Faculty of Dental Surgery Royal College of Surgeons England
5	f3fa4f29-730f-4432-890d-5e65c00e3ec0	Improving Services: Critically Evaluating	Ophthalmology	Online Course	Active	HEE_LDF_04_02	Royal College of Ophthalmologists
6	9932cb8d-6286-426c-8efa-8d5045328bfa	Improving Services: Encouraging Improvement and Innovation	Ophthalmology	Online Course	Active	HEE_LDF_04_03	Royal College of Ophthalmologists
7	b2c5f1db-e829-4f5f-8694-e4cb59e5e41b	Improving Services: Ensuring Patient Safety	Ophthalmology	Online Course	Active	HEE_LDF_04_01	Royal College of Ophthalmologists
8	bafd5651-39c2-4ac4-90b1-6c784ab4587d	Improving Services: Facilitating Transformation	Ophthalmology	Online Course	Active	HEE_LDF_04_04	Royal College of Ophthalmologists
9	860ab130-f13e-4fcc-814a-b64ee4635ef3	Improving the Quality and Safety of Care through Health Informatics	Ophthalmology	Online Course	Active	HEE_INF_01_04	NHS Health Education England
10	0f2a0380-1c38-4c43-928f-dc3b55d2c2f4	IMRT Reporting and Prescribing	Advanced Radiotherapy	Online Course	Active	HEE_ART_02_009	Royal College of Radiologists
11	086fc622-e2c4-40ad-b686-9d9281869300	Inactivated Flu Vaccines	Immunisation - FLU	Online Course	Active	HEE_FLU_02_001	NHS Health Education England
12	d07a8b0f-379f-4872-a838-a47280d70cf8	Inactivated Flu Vaccines - Self-assessment	Immunisation - FLU	Online Course	Active	HEE_FLU_02_002	NHS Health Education England
13	055a4282-33d0-4c43-9431-62e99db4f188	Incision and Closure of Skin and Subcutaneous Tissue	Surgery	Online Course	Active	HEE_SUR_03_002	Royal College of Surgeons
14	7786ca50-d69c-4504-835f-b02338a77c91	Increased Density or Thickening of the Skull Vault or Base	Radiology	Online Course	Active	HEE_RITI_05_091	Royal College of Radiologists
15	c65ce8fa-fe5e-4f3f-aab3-58d10215c042	In-Depth Knowledge of Analgesics	Emergency Care	Online Course	Active	HEE_PRM_05_003	College of Paramedics
16	7889c1ce-e5d0-49ab-8afa-720b348d5be2	Index of Orthodontic Treatment Need	Dentistry	Online Course	Active	HEE_DEN_07_025	Faculty of Dental Surgery Royal College of Surgeons England
17	6dfd0431-af2f-46ff-be42-0ef1ddef3d6b	Indication for Postoperative Critical Care	Anaesthesia	Online Course	Active	HEE_ANA18_03_046	Royal College of Anaesthetists
18	c222d649-74b6-409b-9a97-407e95688fe1	Indications and Contraindications for Space Maintenance	Dentistry	Online Course	Active	HEE_DEN_07_010	Faculty of Dental Surgery Royal College of Surgeons England
19	a973a118-ab8b-4a88-ba3a-372970d3a6be	Indications and Contraindications for/to Peripheral Nerve Blocks (PNBs)	Anaesthesia	Online Course	Active	HEE_ANA_05b_013	Royal College of Anaesthetists
20	6a841ac3-f63e-4d74-ba2a-134f31b6c248	Indications and Evidence for Proton Beam Therapy	Advanced Radiotherapy	Online Course	Active	HEE_ART_06_002	Royal College of Radiologists
21	12cd52a8-f7a3-4d6e-8417-6ae470f30b05	Indications for Central Venous Cannulation	Anaesthesia	Online Course	Active	HEE_ANA_03_014	Royal College of Anaesthetists
22	14234fff-bcc7-4d12-b421-177a4d989f63	Indications For Postoperative Admission	Anaesthesia	Online Course	Active	HEE_ANA_03_046	Royal College of Anaesthetists
23	5633af99-ecf6-43bc-ac50-17e78403adfd	Indications for Using Uncrossed Matched Blood	Surgery	Online Course	Active	HEE_SUR_06_007	Royal College of Surgeons
CourseList

 

[DEEJ3028]

Try changing the last value in the formula from 1 to 0. 0 = exact match lookup, while 1 is not.

Thanks for the suggestion but there will be no exact matches in the sheet.

 

[Peter_SSs]

Thanks for the sample data. Remember next time though that you can hide irrelevant columns etc. That keeps the thread simpler to look through.

Thanks for the suggestion but there will be no exact matches in the sheet.

So, if the value being looked for cannot be found, what is the logic for deciding which row should be used?

 

[DEEJ3028]

Thanks for the sample data. Remember next time though that you can hide irrelevant columns etc. That keeps the thread simpler to look through.


So, if the value being looked for cannot be found, what is the logic for deciding which row should be used?

That is why we are using the TRUE,1 value in the lookup. We get the information from the sheet tab name which truncates the course name. If the value is not found it will report the error. In the example provide the correct result is on row 15 and the result should be the College of Paramedics but returns the value in the row above, Royal College of Radiologists. I have confirmed it as being this value by changing the VLookup to return the value in col 5.

 

[Alex Blakenburg]

It is very hard to work out a pattern from only one example but assuming all your names are truncated in your main sheet, try this:

=VLOOKUP($A2&"*",CourseList!B$2:G$23,6,0)

 

[Peter_SSs]

We get the information from the sheet tab name which truncates the course name.

OK, I had not caught on to that. Try this instead.

=VLOOKUP($A2&"*",CourseList!B$2:G$23,6,0)

More efficient with your version though would be

=XLOOKUP(A2&"*",CourseList!B$2:B$23,CourseList!G$2:G$23,,2)