Displaying Cell based on text

[JLazey]

Hello, I have tried VLOOKUP, HLOOKUP, and too many others to list. I think it will be easier to show:

So, I have a spread sheet with several different hardware and hundreds of serial numbers. i would like to type in the serial number and see the hardware displayed.

 

[arc6th]

If it's just 3 columns of hardware then...unsophisticated...down and dirty...nested Matches. There must be something better to allow for far more columns.

Book1
	A	B	C	D	E	F
1	SN	2363		HA	HB	HC
2	Type	HB		1234	2345	3456
3				1235	2346	3457
4				1236	2347	3458
5				1237	2348	3459
6				1238	2349	3460
7				1239	2350	3461
8				1240	2351	3462
9				1241	2352	3463
10				1242	2353	3464
11				1243	2354	3465
12				1244	2355	3466
13				1245	2356	3467
14				1246	2357	3468
15				1247	2358	3469
16				1248	2359	3470
17				1249	2360	3471
18				1250	2361	3472
19				1251	2362	3473
20				1252	2363	3474
21
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(ISERROR(MATCH(B1,D:D,0)),IF(ISERROR(MATCH(B1,E:E,0)),IF(ISERROR(MATCH(B1,F:F,0)),"ERROR",F1),E1),D1)

 

[JLazey]

There are about 7 columns.

 

[arc6th]

If there are exactly 7 columns just add 4 more nests. Maybe Office 365 has a fancy way to pinpoint a location within the proverbial haystack.

Book1
	A	B	C	D	E	F	G	H	I	J
1	SN	6807		HA	HB	HC	HD	HE	HF	HG
2	Type	HF		1234	7890	3456	5678	4567	6789	2345
3				1235	7891	3457	5679	4568	6790	2346
4				1236	7892	3458	5680	4569	6791	2347
5				1237	7893	3459	5681	4570	6792	2348
6				1238	7894	3460	5682	4571	6793	2349
7				1239	7895	3461	5683	4572	6794	2350
8				1240	7896	3462	5684	4573	6795	2351
9				1241	7897	3463	5685	4574	6796	2352
10				1242	7898	3464	5686	4575	6797	2353
11				1243	7899	3465	5687	4576	6798	2354
12				1244	7900	3466	5688	4577	6799	2355
13				1245	7901	3467	5689	4578	6800	2356
14				1246	7902	3468	5690	4579	6801	2357
15				1247	7903	3469	5691	4580	6802	2358
16				1248	7904	3470	5692	4581	6803	2359
17				1249	7905	3471	5693	4582	6804	2360
18				1250	7906	3472	5694	4583	6805	2361
19				1251	7907	3473	5695	4584	6806	2362
20				1252	7908	3474	5696	4585	6807	2363
21				1253	7909	3475	5697	4586	6808	2364
Sheet1

Cell Formulas
Range	Formula
B2	B2	=IF(ISERROR(MATCH(B1,D:D,0)),IF(ISERROR(MATCH(B1,E:E,0)),IF(ISERROR(MATCH(B1,F:F,0)),IF(ISERROR(MATCH(B1,G:G,0)),IF(ISERROR(MATCH(B1,H:H,0)),IF(ISERROR(MATCH(B1,I:I,0)),IF(ISERROR(MATCH(B1,J:J,0)),"ERROR",J1),I1),H1),G1),F1),E1),D1)

 

[JLazey]

Thank you very much for your help. I keep getting a return of "error". Your formula looks good, I just cant get it to return the value I need it to. Am I missing something? I've tried changing the "error" in the formula to the cells I want displayed to no avail.

 

[JLazey]

Here is the actual formula I am using. I've managed to fix the "error" issue, but it doesn't display the correct hardware consistently.
=IF(ISERROR(MATCH(U4,B24:B53,0)),IF(ISERROR(MATCH(U4,C24:C53,0)),IF(ISERROR(MATCH(U4,D24:D53,0)),IF(ISERROR(MATCH(U4,E24:E53,0)),IF(ISERROR(MATCH(U4,F24:F53,0)),IF(ISERROR(MATCH(U4,H24:H53,0)),"ERROR",H21),F21),E21),D21),C21),B21)

 

[arc6th]

My choice to use the word "ERROR" in the formula was not great. Change it to "Not Found." Then we know if the serial number was found or not.

Column G is skipped?

 

[JLazey]

My choice to use the word "ERROR" in the formula was not great. Change it to "Not Found." Then we know if the serial number was found or not.

Column G is skipped?

Yes, the column is skipped because of a merged cell lower on the sheet. I changed it to "N/A". Its still inconsistent with returning results. i have several cells running that formula and sometimes it returns results and sometimes it doesnt, even though the formulas seems to be the same.

 

[arc6th]

You have the formula looking in the hardware columns between rows 24 & 53. That's a set of 30 serial numbers in each column. If there are only 30 then I'd look at the dataset. Might there might be occasional, stray spaces at the end of the serial numbers in the hardware columns?

 

[JLazey]

You have the formula looking in the hardware columns between rows 24 & 53. That's a set of 30 serial numbers in each column. If there are only 30 then I'd look at the dataset. Might there might be occasional, stray spaces at the end of the serial numbers in the hardware columns?

No, I just relooked through all of them. Its really odd