Vlookup nor Index/Match not working for me

[JeffFinnan]

I have tried vlookup and index/match combinations and always get #N/A.

What I want to achieve is to find an item in column P and then be able to call up any other value in that particular row. For example I may want the date for GS001.

turtleNestCountGTM-2022.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	GS001	#N/A							323060	Guana River South	St. Johns	2	FB		FCGS001	4/19/2022	2022	4	16	109	2459689		N
2									323072	Guana River South	St. Johns	2	N	1	GS001	5/1/2022	2022	5	18	121	2459701	Top of clutch 52 cm, bottom of clutch 90 cm. Egg count includes multitude of spacers. Relocated near South Boardwalk North Beach to 30' 4' 24' -81' 20' 4'	N
3									323075	Guana River South	St. Johns	3	N	2	GS002	5/1/2022	2022	5	18	121	2459701		N
4									323227	Guana Tolomato Matanzas NERR	St. Johns	1	N	1	GN001	5/6/2022	2022	5	18	126	2459706		N
5									323228	Guana Tolomato Matanzas NERR	St. Johns	2	FA		FCGN001	5/6/2022	2022	5	18	126	2459706	30m above HTL -1.5m to dune toe quick turn in dune grass (staked)	N
6									323243	Guana River South	St. Johns	4	N	3	GS003	5/7/2022	2022	5	18	127	2459707	15cm clutch depth. Nest was also located in the recently renourished area. ONA.	N
7									323244	Guana River South	St. Johns	5	FA		FCGS002	5/7/2022	2022	5	18	127	2459707	Simple turnaround and returned to water.	N
8									323281	Guana River South	St. Johns	6	N	4	GS004	5/9/2022	2022	5	19	129	2459709		N
9									323282	Guana River South	St. Johns	7	FA		FCGS003	5/9/2022	2022	5	19	129	2459709		N
10									323341	Guana Tolomato Matanzas NERR	St. Johns	3	N	2	GN002	5/10/2022	2022	5	19	130	2459710	Clutch at 30 cm	N
11									323461	Guana Tolomato Matanzas NERR	St. Johns	4	N	3	GN003	5/13/2022	2022	5	19	133	2459713	47cm clutch depth.	N
12									323534	Guana River South	St. Johns	8	N	5	GS005	5/14/2022	2022	5	19	134	2459714	38cm clutch depth	N
13									323535	Guana Tolomato Matanzas NERR	St. Johns	5	N	4	GN004	5/14/2022	2022	5	19	134	2459714	25cm clutch depth. Old nest likely laid 5/13/22	N
14									323536	Guana Tolomato Matanzas NERR	St. Johns	6	N	5	GN005	5/14/2022	2022	5	19	134	2459714	three separate body pits with partial egg chambers.	N
15									323604	Guana River South	St. Johns	9	FA		FCGS004	5/15/2022	2022	5	20	135	2459715	No relocation needed	N
16									323606	Guana River South	St. Johns	10	N	6	GS006	5/15/2022	2022	5	20	135	2459715	Non-verified	N
17									323607	Guana River South	St. Johns	11	N	7	GS007	5/15/2022	2022	5	20	135	2459715	43 cm depth	N
18									323609	Guana Tolomato Matanzas NERR	St. Johns	7	N	6	GN006	5/15/2022	2022	5	20	135	2459715	Verified, clutch depth 23 cm	N
19									323610	Guana Tolomato Matanzas NERR	St. Johns	8	N	7	GN007	5/15/2022	2022	5	20	135	2459715	Non-verified	N
20									323777	Guana Tolomato Matanzas NERR	St. Johns	9	FA		FCGN002	5/16/2022	2022	5	20	136	2459716		N
21									323779	Guana Tolomato Matanzas NERR	St. Johns	10	N	8	GN008	5/16/2022	2022	5	20	136	2459716		N
South 2022

Cell Formulas
Range	Formula
C1	C1	=INDEX(P1:P21,MATCH(B1,J1:X21,0))

Thanks,
Jeff

 

[Ezguy4u]

Jeff let's get the ball rolling. Here is my solution. If it doesn't work, let us know. Also I used list under data Validation.

Book1
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	GS002	5/1/2022							323060	Guana River South	St. Johns	2	FB		FCGS001	4/19/2022	2022	4	16	109	2459689		N
2									323072	Guana River South	St. Johns	2	N	1	GS001	5/1/2022	2022	5	18	121	2459701	Top of clutch 52 cm, bottom of clutch 90 cm. Egg count includes multitude of spacers. Relocated near South Boardwalk North Beach to 30' 4' 24' -81' 20' 4'	N
3									323075	Guana River South	St. Johns	3	N	2	GS002	5/1/2022	2022	5	18	121	2459701		N
4									323227	Guana Tolomato Matanzas NERR	St. Johns	1	N	1	GN001	5/6/2022	2022	5	18	126	2459706		N
5									323228	Guana Tolomato Matanzas NERR	St. Johns	2	FA		FCGN001	5/6/2022	2022	5	18	126	2459706	30m above HTL -1.5m to dune toe quick turn in dune grass (staked)	N
6									323243	Guana River South	St. Johns	4	N	3	GS003	5/7/2022	2022	5	18	127	2459707	15cm clutch depth. Nest was also located in the recently renourished area. ONA.	N
7									323244	Guana River South	St. Johns	5	FA		FCGS002	5/7/2022	2022	5	18	127	2459707	Simple turnaround and returned to water.	N
8									323281	Guana River South	St. Johns	6	N	4	GS004	5/9/2022	2022	5	19	129	2459709		N
9									323282	Guana River South	St. Johns	7	FA		FCGS003	5/9/2022	2022	5	19	129	2459709		N
10									323341	Guana Tolomato Matanzas NERR	St. Johns	3	N	2	GN002	5/10/2022	2022	5	19	130	2459710	Clutch at 30 cm	N
11									323461	Guana Tolomato Matanzas NERR	St. Johns	4	N	3	GN003	5/13/2022	2022	5	19	133	2459713	47cm clutch depth.	N
12									323534	Guana River South	St. Johns	8	N	5	GS005	5/14/2022	2022	5	19	134	2459714	38cm clutch depth	N
13									323535	Guana Tolomato Matanzas NERR	St. Johns	5	N	4	GN004	5/14/2022	2022	5	19	134	2459714	25cm clutch depth. Old nest likely laid 5/13/22	N
14									323536	Guana Tolomato Matanzas NERR	St. Johns	6	N	5	GN005	5/14/2022	2022	5	19	134	2459714	three separate body pits with partial egg chambers.	N
15									323604	Guana River South	St. Johns	9	FA		FCGS004	5/15/2022	2022	5	20	135	2459715	No relocation needed	N
16									323606	Guana River South	St. Johns	10	N	6	GS006	5/15/2022	2022	5	20	135	2459715	Non-verified	N
17									323607	Guana River South	St. Johns	11	N	7	GS007	5/15/2022	2022	5	20	135	2459715	43 cm depth	N
18									323609	Guana Tolomato Matanzas NERR	St. Johns	7	N	6	GN006	5/15/2022	2022	5	20	135	2459715	Verified, clutch depth 23 cm	N
19									323610	Guana Tolomato Matanzas NERR	St. Johns	8	N	7	GN007	5/15/2022	2022	5	20	135	2459715	Non-verified	N
20									323777	Guana Tolomato Matanzas NERR	St. Johns	9	FA		FCGN002	5/16/2022	2022	5	20	136	2459716		N
21									323779	Guana Tolomato Matanzas NERR	St. Johns	10	N	8	GN008	5/16/2022	2022	5	20	136	2459716		N
Data

Cell Formulas
Range	Formula
C1	C1	=INDEX(Q1:Q21,MATCH(B1,P1:P21,0),1)

 

[JeffFinnan]

Jeff let's get the ball rolling. Here is my solution. If it doesn't work, let us know. Also I used list under data Validation.

This worked very well. Now I have to study why it works.

Thanks much,
Jeff

 

[Peter_SSs]

find an item in column P and then be able to call up any other value in that particular row.

I see that you have a working suggestion but it requires you to manually decide which column to look in to get the desired result. I'm wondering if your sample is actually like you have or whether there are, or could be, headings. I have just made some up below bit if you have those then you can use them to get the formula to determine which column to read the results from.

22 05 27.xlsm
	B	C	D						J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	GS001	Date	1/05/2022						ID	Loc 1	Loc 2	Idx	Code	Sub	Term	Date	Yr	C1	C2	C3	Amt	Notes	Dir
2	GN005	ID	323536						323060	Guana River South	St. Johns	2	FB		FCGS001	19/04/2022	2022	4	16	109	2459688.5		N
3	FCGN002	Code	FA						323072	Guana River South	St. Johns	2	N	1	GS001	1/05/2022	2022	5	18	121	2459700.5	Top of clutch 52 cm, bottom of clutch 90 cm. Egg count includes multitude of spacers. Relocated near South Boardwalk North Beach to 30' 4' 24' -81' 20' 4'	N
4									323075	Guana River South	St. Johns	3	N	2	GS002	1/05/2022	2022	5	18	121	2459700.5		N
5									323227	Guana Tolomato Matanzas NERR	St. Johns	1	N	1	GN001	6/05/2022	2022	5	18	126	2459705.5		N
6									323228	Guana Tolomato Matanzas NERR	St. Johns	2	FA		FCGN001	6/05/2022	2022	5	18	126	2459705.5	30m above HTL -1.5m to dune toe quick turn in dune grass (staked)	N
7									323243	Guana River South	St. Johns	4	N	3	GS003	7/05/2022	2022	5	18	127	2459706.5	15cm clutch depth. Nest was also located in the recently renourished area. ONA.	N
8									323244	Guana River South	St. Johns	5	FA		FCGS002	7/05/2022	2022	5	18	127	2459706.5	Simple turnaround and returned to water.	N
9									323281	Guana River South	St. Johns	6	N	4	GS004	9/05/2022	2022	5	19	129	2459708.5		N
10									323282	Guana River South	St. Johns	7	FA		FCGS003	9/05/2022	2022	5	19	129	2459708.5		N
11									323341	Guana Tolomato Matanzas NERR	St. Johns	3	N	2	GN002	10/05/2022	2022	5	19	130	2459709.5	Clutch at 30 cm	N
12									323461	Guana Tolomato Matanzas NERR	St. Johns	4	N	3	GN003	13/05/2022	2022	5	19	133	2459712.5	47cm clutch depth.	N
13									323534	Guana River South	St. Johns	8	N	5	GS005	14/05/2022	2022	5	19	134	2459713.5	38cm clutch depth	N
14									323535	Guana Tolomato Matanzas NERR	St. Johns	5	N	4	GN004	14/05/2022	2022	5	19	134	2459713.5	25cm clutch depth. Old nest likely laid 5/13/22	N
15									323536	Guana Tolomato Matanzas NERR	St. Johns	6	N	5	GN005	14/05/2022	2022	5	19	134	2459713.5	three separate body pits with partial egg chambers.	N
16									323604	Guana River South	St. Johns	9	FA		FCGS004	15/05/2022	2022	5	20	135	2459714.5	No relocation needed	N
17									323606	Guana River South	St. Johns	10	N	6	GS006	15/05/2022	2022	5	20	135	2459714.5	Non-verified	N
18									323607	Guana River South	St. Johns	11	N	7	GS007	15/05/2022	2022	5	20	135	2459714.5	43 cm depth	N
19									323609	Guana Tolomato Matanzas NERR	St. Johns	7	N	6	GN006	15/05/2022	2022	5	20	135	2459714.5	Verified, clutch depth 23 cm	N
20									323610	Guana Tolomato Matanzas NERR	St. Johns	8	N	7	GN007	15/05/2022	2022	5	20	135	2459714.5	Non-verified	N
21									323777	Guana Tolomato Matanzas NERR	St. Johns	9	FA		FCGN002	16/05/2022	2022	5	20	136	2459715.5		N
22									323779	Guana Tolomato Matanzas NERR	St. Johns	10	N	8	GN008	16/05/2022	2022	5	20	136	2459715.5		N
Lookup Column

Cell Formulas
Range	Formula
D1:D3	D1	=INDEX(J$2:X$22,MATCH(B1,P$2:P$22,0),MATCH(C1,J$1:X$1,0))