Assist with creating a formula to select a table to perform a Vlookup based on two conditions.

Good day

The client wishes to create a template to assist with comparison quotes in the insurance industry. The client has an additional PDF document, containing Excess and Limit criteria. Which I have started to populate in the Limits & Excess tab. The template should be able to pull data from this tab into the template tab called 'Quote_Print-out'. In an attempt to resolve the challenges, I have created a sheet called MasterKeys.

[https://centdel-my.sharepoint.com/:x:/g/personal/marinda_steenkamp_ipmg_co_za/ERRPHBHGbypEhgIEQUb43bEB0DrhmpQ_amJdXVWGsOvI-A?e=dYCaTN]

Example to explain requirement:

B16 contains a drop-down list that refers to the Geyser types on the 'Limits & Excess!A16:D29'
- This has been converted into tables in the MasterKeys sheet
C16 select the sub-categories based on the selection in B16.

Challenge

In E16, I wish to return the value in 'Limits & Excess!B:B' based on the sub-categories or the relevant data in the MasterKeys tab based on the relevant table.

Your assistance would be most appreciated.

Kind regards

Marinda

excel

Excel for web

Formulas and Functions

Patrick2788

Feb 05, 2024

MarindaS

If I understand the objective clearly, I think this will do it:

=LET(
    area, XMATCH(B16, GeyserType),
    r, MAX(N(C16 = MasterKeys!$O$2:$R$5) * {1; 2; 3; 4}),
    limit, IFERROR(INDEX((Table2, Table24, Table4, Table5), r, 2, area), ""),
    IF(COUNTBLANK(B16:C16) >= 1, "", limit)
)

You may want to re-name your tables to give them descriptive names and then this formula reads even better.

=LET( area, XMATCH(B16, GeyserType), r, MAX(N(C16 = MasterKeys!$O$2:$R$5) * {1; 2; 3; 4}), limit, IFERROR(INDEX((Table2, Table24, Table4, Table5), r, 2, area), ""), IF(COUNTBLANK(B16:C16) >= 1, "", limit) )

Forum Discussion

Assist with creating a formula to select a table to perform a Vlookup based on two conditions.

3 Replies