Multiple VLookup IF Statements

%3CLINGO-SUB%20id%3D%22lingo-sub-2818161%22%20slang%3D%22en-US%22%3EMultiple%20VLookup%20IF%20Statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2818161%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20struggling%20to%20figure%20out%20the%20best%20way%20to%20right%20a%20formula%20that%20would%20likely%20require%20multiple%20lookups.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20are%20trying%20to%20pull%20our%20insurance%20premiums%20for%20our%20employees%20so%20it%20will%20be%20based%20on%20their%20choices%2C%20so%20it%20will%20require%20multiple%20conditions%20and%20possible%20look%20up%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20first%20item%20to%20look%20up%20is%20the%20age%20and%20then%20based%20on%20the%20age%20and%20insurance%20election%2C%20return%20the%20value%20associated%20with%20that%20age%20group%20and%20insurance%20selection.%26nbsp%3B%20There%20are%204%20insurance%20selections.%26nbsp%3B%20I%20was%20thinking%20a%20nested%20vlookup%2C%20but%20I%20will%20need%20an%20IF%20statement%20to%20look%20up%20the%20age%20and%20the%20insurance%20plan%20and%20then%20return%20the%20value%20and%20an%20I%20am%20struggling%20to%20figure%20out%20the%20best%20way%20to%20tackle%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20please%20help%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2818161%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2819412%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20VLookup%20IF%20Statements%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2819412%22%20slang%3D%22en-US%22%3EA%20suggestion%3A%20JPGs%20are%20only%20of%20limited%20value.%20If%20you%20have%20a%20%22dummy%22%20workbook%20that%20you%20could%20post--dummy%20in%20the%20sense%20that%20no%20confidential%20or%20proprietary%20info%20is%20disclosed--that%20would%20be%20far%20more%20helpful.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20sense%20(as%20a%20former%20HR%20systems%20director)%20is%20that%20it%20might%20be%20more%20accessible%20to%20think%20of%20writing%20two%20or%20more%20formulas%2C%20formulas%20that%20build%20on%20one%20another.%20It%20could%20well%20be%20possible%20to%20write%20a%20single%20formula%2C%20deeply%20nested%20conditions%20and%20criteria%2C%20but%20that%20also%20approaches%20the%20point%20of%20unintelligibility.%3CBR%20%2F%3E%3CBR%20%2F%3ESo%2C%20for%20example%2C%20a%20first%20lookup%20that%20is%20based%20on%20age%20puts%20the%20person%20into%20an%20age%20band%2C%20another%20secondlookup%20uses%20that%20age%20band%20as%20a%20criterion%20for%20the%20plan%20table%20to%20find%20the%20appropriate%20rate.%3CBR%20%2F%3E%3CBR%20%2F%3EBut%20as%20note%2C%20if%20you%20could%20post%20a%20dummy%20workbook%2C%20we%20could%20work%20with%20some%20representative%20tables%20rather%20than%20just%20with%20abstract%20concepts.%3C%2FLINGO-BODY%3E
New Contributor

I am struggling to figure out the best way to right a formula that would likely require multiple lookups.

 

We are trying to pull our insurance premiums for our employees so it will be based on their choices, so it will require multiple conditions and possible look up formulas.

 

The first item to look up is the age and then based on the age and insurance election, return the value associated with that age group and insurance selection.  There are 4 insurance selections.  I was thinking a nested vlookup, but I will need an IF statement to look up the age and the insurance plan and then return the value and an I am struggling to figure out the best way to tackle this.

 

Can someone please help?

 

Thanks

1 Reply
A suggestion: JPGs are only of limited value. If you have a "dummy" workbook that you could post--dummy in the sense that no confidential or proprietary info is disclosed--that would be far more helpful.

My sense (as a former HR systems director) is that it might be more accessible to think of writing two or more formulas, formulas that build on one another. It could well be possible to write a single formula, deeply nested conditions and criteria, but that also approaches the point of unintelligibility.

So, for example, a first lookup that is based on age puts the person into an age band, another secondlookup uses that age band as a criterion for the plan table to find the appropriate rate.

But as note, if you could post a dummy workbook, we could work with some representative tables rather than just with abstract concepts.