Return Blank Cell - VLOOKUP from Combo Box Linked Cell

[Duncan20]

I am using the following formula to output data from a lookup table.

=(IFERROR(LOOKUP($A$11+0,ID,DOM),(IFERROR(LOOKUP($A$11,ID,DOM),""))))

As I am using a Combo Box (Active X) to select the criteria linked to cell A11 - I have had to add the ISERROR combinations as the lookup data varies between text and numbers.

This formula is working fine but when there is no data in the lookup (DOM) I would like to output a blank cell.

I have tried all variations and combinations of ISBLANK but cannot get the right result -

Any ideas??

 

[Fluff]

How about
=IFERROR(LOOKUP(IFERROR($A$11+0,$A$11),ID,DOM),"")

 

[Duncan20]

Thanks for the suggestion Fluff - I gave it a go but it returns a blank cell even if there is data in the lookup table..

 

[Fluff]

In that case can you please post some sample data using the XL2BB add-in.
Also can you please update your account details to show which version of Xl you are using & what platform.

 

[Duncan20]

The below is Sheet 1 (Certificate)

Tackle List & Certificate Creator.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
10	ID No. (3)	Certificate No.		Description of Equipment	Date of Manufacture (3)	Date of Last Examination (4)	SWL (5)	Length	Examination Type (7a i, ii, iii, iv)		Defect & Particulars of any repair, renewal or alteration (8a, b)	Latest Date of Next Examination (8d)
11	14287638	LGS1385012		Webb Sling			10 Ton	12m	6 Monthly		None	30/05/2020
12	14287639	LGS1385011		Chain	0		20	6m	8 Monthly		Nine	31/05/2020
Certificate

Cell Formulas
Range	Formula
B11	B11	=(IFERROR(LOOKUP($A$11+0,ID,Cert_No),(IFERROR(LOOKUP($A$11,ID,Cert_No),""))))
D11	D11	=(IFERROR(LOOKUP($A$11+0,ID,Description),(IFERROR(LOOKUP($A$11,ID,Description),""))))
E11	E11	=IFERROR(LOOKUP(IFERROR($A$11+0,$A$11),ID,DOM),"")
B12	B12	=(IFERROR(LOOKUP($A$12+0,ID,Cert_No),(IFERROR(LOOKUP($A$12,ID,Cert_No),""))))
D12	D12	=(IFERROR(LOOKUP($A$12+0,ID,Description),(IFERROR(LOOKUP($A$12,ID,Description),""))))
E12	E12	=(IFERROR(LOOKUP($A$12+0,ID,DOM),(IFERROR(LOOKUP($A$12,ID,DOM),""))))
G11	G11	=(IFERROR(LOOKUP($A$11+0,ID,SWL),(IFERROR(LOOKUP($A$11,ID,SWL),""))))
H11	H11	=(IFERROR(LOOKUP($A$11+0,ID,Length),(IFERROR(LOOKUP($A$11,ID,Length),""))))
I11	I11	=(IFERROR(LOOKUP($A$11+0,ID,Type),(IFERROR(LOOKUP($A$11,ID,Type),""))))
K11	K11	=(IFERROR(LOOKUP($A$11+0,ID,Defect),(IFERROR(LOOKUP($A$11,ID,Defect),""))))
L11	L11	=(IFERROR(LOOKUP($A$11+0,ID,Next_Exam),(IFERROR(LOOKUP($A$11,ID,Next_Exam),""))))
G12	G12	=(IFERROR(LOOKUP($A$12+0,ID,SWL),(IFERROR(LOOKUP($A$12,ID,SWL),""))))
H12	H12	=(IFERROR(LOOKUP($A$12+0,ID,Length),(IFERROR(LOOKUP($A$12,ID,Length),""))))
I12	I12	=(IFERROR(LOOKUP($A$12+0,ID,Type),(IFERROR(LOOKUP($A$12,ID,Type),""))))
K12	K12	=(IFERROR(LOOKUP($A$12+0,ID,Defect),(IFERROR(LOOKUP($A$12,ID,Defect),""))))
L12	L12	=(IFERROR(LOOKUP($A$12+0,ID,Next_Exam),(IFERROR(LOOKUP($A$12,ID,Next_Exam),""))))

Named Ranges
Name	Refers To	Cells
Defect	='Equipment List'!$J$2:$J$4001	K11:K12
Description	='Equipment List'!$D$2:$D$4001	D11:D12
DOM	='Equipment List'!$E$2:$E$4001	E11:E12
ID	='Equipment List'!$B$2:$B$4001	G11:L12, D11:E12
Length	='Equipment List'!$H$2:$H$4001	H11:H12
Next_Exam	='Equipment List'!$K$2:$K$4001	L11:L12
SWL	='Equipment List'!$G$2:$G$4001	G11:G12
Type	='Equipment List'!$I$2:$I$4001	I11:J12

This is the data from sheet 2 (Equipment List)

Tackle List & Certificate Creator.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Location	ID No. (3)	Certificate No.	Description of Equipment	Date of Manufacture (3)	Date of Last Examination (4)	SWL (5)	Length	Examination Type (7a i, ii, iii, iv)	Defect & Particulars of any repair, renewal or alteration (8a, b)	Latest Date of Next Examination (8d)
2
3	CARMARTHEN YARD	14287638	LGS1385012	Webb Sling	14/09/1983	30/05/2019	10 Ton	12m	6 Monthly	None	30/05/2020
4	M82	14287639	LGS1385011	Chain		31/05/2019	20	6m	8 Monthly	Nine	31/05/2020
Equipment List

Cells with Data Validation
Cell	Allow	Criteria
A3:A4	List	=Location

on Sheet 1 (Certificate) E12 is the cell where I need the formula to report blank.

On sheet 2 (Equipment List) the lookup is retrieving from E4

Mnay thanks

 

[Fluff]

If you want to return "" when a value exists in the lookup table, but has a blank cell in the return, use

+Fluff.xlsm
	A	B	C	D	E
10	ID No. (3)	Certificate No.		Description of Equipment	Date of Manufacture (3)
11	14287638				14/09/1983
12	14287639
List

Cell Formulas
Range	Formula
E11:E12	E11	=IFERROR(1/(1/LOOKUP(IFERROR($A11+0,$A11),ID,DOM)),"")

 

[Duncan20]

Thanks Fluff,

I have just tried this formula but it outputs a blank cell regardless of whether there is data in the lookup..

I have taken a couple screenshots of both scenarios for reference..

 

[Fluff]

In that case make sure that the IDs on the equipment list are all real numbers, rather than numbers stored as text