VLOOK-UP Formula

[cfhasan89]

I trying to VLOOKUP the serial number & Description from List_tbl and add to Installed_tbl. If a serial number is available return serial, if not return "EMPTY".

List_tbl
[TABLE="width: 1027"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]FLEET[/TD]
[TD="align: left"]BASE[/TD]
[TD="align: left"]SRD[/TD]
[TD="align: left"]AIRCRAFT[/TD]
[TD="align: left"]WUC[/TD]
[TD="align: left"]POSITION[/TD]
[TD="align: left"]UNIQUE_ID[/TD]
[TD="align: left"]ALT_POSITION[/TD]
[TD="align: left"]DESCRIPTION[/TD]
[TD="align: left"]SERIAL[/TD]
[/TR]
[TR]
[TD="align: left"]A-01 R[/TD]
[TD="align: right"]5[/TD]
[TD="align: left"]ABC[/TD]
[TD="align: left"]JMA0601[/TD]
[TD="align: left"]11000[/TD]
[TD][/TD]
[TD="align: left"]11000[/TD]
[TD="align: right"]0[/TD]
[TD="align: left"]F-15S AIRFRAME[/TD]
[TD="align: left"]93-0864[/TD]
[/TR]
[TR]
[TD="align: left"]A-01 R[/TD]
[TD="align: right"]5[/TD]
[TD="align: left"]ABC[/TD]
[TD="align: left"]JMA0601[/TD]
[TD="align: left"]11AC0[/TD]
[TD][/TD]
[TD="align: left"]11AC0[/TD]
[TD="align: right"]0[/TD]
[TD="align: left"]RADOME ASSY (DOOR NO.1) (F-15SICR)[/TD]
[TD="align: left"]1757[/TD]
[/TR]
</tbody>[/TABLE]

Installed_tbl
[TABLE="width: 1333"]
<colgroup><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: left"]FLEET[/TD]
[TD="align: left"]BASE[/TD]
[TD="align: left"]SRD[/TD]
[TD="align: left"]AIRCRAFT[/TD]
[TD="align: left"]WUC[/TD]
[TD="align: left"]POSITION[/TD]
[TD="align: left"]UNIQUE_ID[/TD]
[TD="align: left"]ALT_POSITION[/TD]
[TD="align: left"]DESCRIPTION[/TD]
[TD="align: left"]SERIAL[/TD]
[/TR]
[TR]
[TD="align: left"]A-01 R[/TD]
[TD="align: right"]5[/TD]
[TD="align: left"]ABC[/TD]
[TD="align: left"]JMA0601[/TD]
[TD="align: left"]11000[/TD]
[TD][/TD]
[TD="align: left"]11000[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I've tried the following for the serial number but having issues.

My formula: =IF(ISERROR(VLOOKUO($G2,List_tbl!$G2:List_tbl!$G21028,1,FALSE)),"EMPTY",J2)

 

[AlanY]

try

=IFERROR(VLOOKUP($G2,List_tbl!$G2:List_tbl!$J21028,4,FALSE),"EMPTY")

 

[cfhasan89]

This place "EMPTY" is each cell. If a match bring back serial number

 

[AlanY]

try this

Book1
	A	B	C	D	E	F	G	H	I	J
1	FLEET	BASE	SRD	AIRCRAFT	WUC	POSITION	UNIQUE_ID	ALT_POSITION	DESCRIPTION	SERIAL
2	A-01 R	5	ABC	JMA0601	11000		11000	0	F-15S AIRFRAME	93-0864
3	A-01 R	5	ABC	JMA0601	11AC0		11AC0	0	RADOME ASSY (DOOR NO.1) (F-15SICR)	1757
List_tbl

Book1
	A	B	C	D	E	F	G	H	I	J
1	FLEET	BASE	SRD	AIRCRAFT	WUC	POSITION	UNIQUE_ID	ALT_POSITION	DESCRIPTION	SERIAL
2	A-01 R	5	ABC	JMA0601	11000		11000	0	F-15S AIRFRAME	93-0864
Installed_tbl

Cell Formulas
Range	Formula
I2	=IFERROR(VLOOKUP($G2,List_tbl!$G2:List_tbl!$J21028,3,FALSE),"EMPTY")
J2	=IFERROR(VLOOKUP($G2,List_tbl!$G2:List_tbl!$J21028,4,FALSE),"EMPTY")

 

[cfhasan89]

Same results "EMPTY" for every cell. In the List_tbl I2: ="F-15S AIRFRAME" and J2: ="93-0864" would this cause me issues?

 

[AlanY]

Same results "EMPTY" for every cell. In the List_tbl I2: ="F-15S AIRFRAME" and J2: ="93-0864" would this cause me issues?

i actually cut and copy the data in post#1 that works

have a look to see if you can spot something

https://drive.google.com/file/d/1lsAMbNLIX1YBnu5Me-3aJ0-B_HSNSZKs/view?usp=sharing

 

[cfhasan89]

i actually cut and copy the data in post#1 that works

have a look to see if you can spot something

https://drive.google.com/file/d/1lsAMbNLIX1YBnu5Me-3aJ0-B_HSNSZKs/view?usp=sharing

Here is my file. Could you see if you can spot the error? Also, getting #N/A error.

https://drive.google.com/file/d/11LZVIadA4J1NtUMp-_9FAMPDllmcudN_/view?usp=sharing

 

[AlanY]

your formulas are incorrect with a different way
the 2 formulas should be

Book1
	B	C	D	E	F	G	H	I
1	BASE	SRD	AIRCRAFT	WUC	POS	UNIQUE_ID	DESCRIPTION	SERIAL
2	5	AAB	5QA0601	11AC0		11AC0	RADOME ASSY (DOOR NO.1) (F-15SICR)	1757
3	5	AAB	5QA0601	11GK0	01	11GK001	RT VERT STABLIZER	A44-1045
4	5	AAB	5QA0601	11GK0	02	11GK002	LT VERT STABLIZER	A44-1046
5	5	AAB	5QA0601	11K01		11K01	WING ASSY, LEFT	A24-1322
6	5	AAB	5QA0601	11K02		11K02	WING ASSY, RIGHT	A24-1321
7	5	AAB	5QA0601	12AAN		12AAN	ENVIR CONT PNL, F-15S	50601-04
8	5	AAB	5QA0601	12CA0		12CA0	CANOPY ASSY.	A26-0481
9	5	AAB	5QA0601	12E00	AFT	12E00AFT	AFT EJECTION SEAT	5DA210
10	5	AAB	5QA0601	12E00	FWD	12E00FWD	FWD EJECTION SEAT	5DF206
11	5	AAB	5QA0601	12EE0	AFT	12EE0AFT	STAPAC	CCC5599
Installed_tbl

Cell Formulas
Range	Formula
H2	=IFERROR(VLOOKUP($G2,List_tbl!$G$2:List_tbl!$J$21028,3,FALSE),"EMPTY")
I2	=IFERROR(VLOOKUP($G2,List_tbl!$G$2:List_tbl!$J$21028,4,FALSE),"EMPTY")

 

[AlanY]

the first formula use relative cell reference, $G2:List_tbl!$J21028, that should be fixed, $G$2:List_tbl!$J$21028

=IFERROR(VLOOKUP($G2,List_tbl![COLOR="#FF0000"]$G2:List_tbl!$J21028[/COLOR],3,FALSE),"EMPTY")

and the second refer to a single column, $G$2:List_tbl!$G$21028 instead of 4 columns G to J, $G$2:List_tbl!$J$21028

=IFERROR(VLOOKUP($G2,List_tbl!$[COLOR="#FF0000"]G$2:List_tbl!$G$21028[/COLOR],[COLOR="#FF0000"]3[/COLOR],FALSE),"EMPTY")

and the result column should be 4, not 3.