# Help Please! Vlookup with Checkbox options formula

#### PeteN69

##### New Member
Hi all,

Im trying to create a formula that will use a vlookup table based on a value but will change to another vlookup table when a few optional checkboxes are ticked.
Im sure i need a nested IF / AND / OR formula but i just cant seem to get the basic structure right to start with! I seen the few examples on the site, but i'm struggling to get the formula to work.
Any help will be greatly appreciated - I've attached an example to help describe what I'm trying to do.

Regards,

Pete.

#### Attachments

• 24.2 KB Views: 9

#### AliGW

##### Active Member
Can you explain how/why you would want to use multiple tables (e.g. B+C) in one VLOOKUP formula?

This would be easy to achieve with named ranges.

#### PeteN69

##### New Member
The original data comes from Variable Speed Drives for a series of pumps - so gear ratios against a constant wattage.
im trying to use the vlookup to determine an output that will dynamically show power requirements based on gear setting for costing purposes
The numbers in the spreadsheet are made up, i was trying to show a curved efficiency.

I used named ranges for each of the vlookups - TableA, TableB, TableC and TableB+C.

The value in cell B8 is 4 so TableA (Gear 0) returns a output of 2. If i tick the checkbox B it changes the lookup to look at TableB (Gear 1) which returns a value of 3 and so on, except i cant get the right formula structure. If there is a better way to do it im all ears

The formulas look like they are going to get quite big, so using a series of IF statements were my first choice, until the checkboxes are put in but do not work the way i wanted. I think i will need 3 or 4 vlookups in the formula, driven by the checkboxes...

Pete.

#### bosco_yip

##### Excel Ninja
Try,

In C8, copied down :

=LOOKUP(9^9,CHOOSE(IFERROR(MATCH(BIN2DEC(0+H8&0+I8&0+J8),{0,4,2},0),4),VLOOKUP(B8,TableA,2,1),VLOOKUP(B8,TableB,2,1),VLOOKUP(B8,TableC,2,1),VLOOKUP(B8,TableBC,2,1)))

Regards
Bosco

#### Attachments

• 24.6 KB Views: 7

#### John Jairo V

##### Well-Known Member
Hi, to both!

Continue with @bosco_yip solution... why not this formula?

=VLOOKUP(B8,CHOOSE(IFERROR(MATCH(BIN2DEC(--H8&--I8&--J8),{0;4;2},),4),TableA,TableB,TableC,TableBC),2)

Blessings!

#### PeteN69

##### New Member
Many thanks! @

Both formula work very well. Its going to take me a while to work out how they work, but that it is possible is very encouraging.
Ill have a go at getting rid of the B+C checkbox and just use the two remaining ones for the same effect, but i might be back soon asking for help again.

Many thanks again and best regards,

Pete.