Return Blank Cell - VLOOKUP from Combo Box Linked Cell

Duncan20

New Member
Joined
Feb 12, 2017
Messages
11
Office Version
  1. 365
Platform
  1. Windows
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??
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
How about
=IFERROR(LOOKUP(IFERROR($A$11+0,$A$11),ID,DOM),"")
 
Upvote 0
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..
 
Upvote 0
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.
 
Upvote 0
The below is Sheet 1 (Certificate)

Tackle List & Certificate Creator.xlsm
ABCDEFGHIJKL
10ID No. (3)Certificate No.Description of EquipmentDate of Manufacture (3)Date of Last Examination (4)SWL (5)LengthExamination Type (7a i, ii, iii, iv)Defect & Particulars of any repair, renewal or alteration (8a, b)Latest Date of Next Examination (8d)
1114287638LGS1385012Webb Sling 10 Ton12m6 Monthly None30/05/2020
1214287639LGS1385011Chain0206m8 MonthlyNine31/05/2020
Certificate
Cell Formulas
RangeFormula
B11B11=(IFERROR(LOOKUP($A$11+0,ID,Cert_No),(IFERROR(LOOKUP($A$11,ID,Cert_No),""))))
D11D11=(IFERROR(LOOKUP($A$11+0,ID,Description),(IFERROR(LOOKUP($A$11,ID,Description),""))))
E11E11=IFERROR(LOOKUP(IFERROR($A$11+0,$A$11),ID,DOM),"")
B12B12=(IFERROR(LOOKUP($A$12+0,ID,Cert_No),(IFERROR(LOOKUP($A$12,ID,Cert_No),""))))
D12D12=(IFERROR(LOOKUP($A$12+0,ID,Description),(IFERROR(LOOKUP($A$12,ID,Description),""))))
E12E12=(IFERROR(LOOKUP($A$12+0,ID,DOM),(IFERROR(LOOKUP($A$12,ID,DOM),""))))
G11G11=(IFERROR(LOOKUP($A$11+0,ID,SWL),(IFERROR(LOOKUP($A$11,ID,SWL),""))))
H11H11=(IFERROR(LOOKUP($A$11+0,ID,Length),(IFERROR(LOOKUP($A$11,ID,Length),""))))
I11I11=(IFERROR(LOOKUP($A$11+0,ID,Type),(IFERROR(LOOKUP($A$11,ID,Type),""))))
K11K11=(IFERROR(LOOKUP($A$11+0,ID,Defect),(IFERROR(LOOKUP($A$11,ID,Defect),""))))
L11L11=(IFERROR(LOOKUP($A$11+0,ID,Next_Exam),(IFERROR(LOOKUP($A$11,ID,Next_Exam),""))))
G12G12=(IFERROR(LOOKUP($A$12+0,ID,SWL),(IFERROR(LOOKUP($A$12,ID,SWL),""))))
H12H12=(IFERROR(LOOKUP($A$12+0,ID,Length),(IFERROR(LOOKUP($A$12,ID,Length),""))))
I12I12=(IFERROR(LOOKUP($A$12+0,ID,Type),(IFERROR(LOOKUP($A$12,ID,Type),""))))
K12K12=(IFERROR(LOOKUP($A$12+0,ID,Defect),(IFERROR(LOOKUP($A$12,ID,Defect),""))))
L12L12=(IFERROR(LOOKUP($A$12+0,ID,Next_Exam),(IFERROR(LOOKUP($A$12,ID,Next_Exam),""))))
Named Ranges
NameRefers ToCells
Defect='Equipment List'!$J$2:$J$4001K11:K12
Description='Equipment List'!$D$2:$D$4001D11:D12
DOM='Equipment List'!$E$2:$E$4001E11:E12
ID='Equipment List'!$B$2:$B$4001G11:L12, D11:E12
Length='Equipment List'!$H$2:$H$4001H11:H12
Next_Exam='Equipment List'!$K$2:$K$4001L11:L12
SWL='Equipment List'!$G$2:$G$4001G11:G12
Type='Equipment List'!$I$2:$I$4001I11:J12


This is the data from sheet 2 (Equipment List)

Tackle List & Certificate Creator.xlsm
ABCDEFGHIJK
1LocationID No. (3)Certificate No.Description of EquipmentDate of Manufacture (3)Date of Last Examination (4)SWL (5)LengthExamination Type (7a i, ii, iii, iv)Defect & Particulars of any repair, renewal or alteration (8a, b)Latest Date of Next Examination (8d)
2
3CARMARTHEN YARD14287638LGS1385012Webb Sling14/09/198330/05/201910 Ton12m6 Monthly None30/05/2020
4M8214287639LGS1385011Chain31/05/2019206m8 MonthlyNine31/05/2020
Equipment List
Cells with Data Validation
CellAllowCriteria
A3:A4List=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
 
Upvote 0
If you want to return "" when a value exists in the lookup table, but has a blank cell in the return, use

+Fluff.xlsm
ABCDE
10ID No. (3)Certificate No.Description of EquipmentDate of Manufacture (3)
111428763814/09/1983
1214287639 
List
Cell Formulas
RangeFormula
E11:E12E11=IFERROR(1/(1/LOOKUP(IFERROR($A11+0,$A11),ID,DOM)),"")
 
Upvote 0
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..
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    54.9 KB · Views: 6
  • Capture2.JPG
    Capture2.JPG
    56.5 KB · Views: 6
Upvote 0
In that case make sure that the IDs on the equipment list are all real numbers, rather than numbers stored as text
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top