• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

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

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.
 
Top