VLOOKUP with Multiple Criteria (Checkbox and Text)

[LauraEdson10]

I have the following tick boxes: "New" in Cell B18, "Used" in C18 and "Refurbished" in D18.

I have a "Make" which would just be text entered i.e. "Mitsubishi", "Toyota" etc in B22.

If the "New" checkbox is ticked in B18 and a Make is entered into B22, I want it to do a VLOOKUP to my "Warranty" sheet.

For example,
a New Cesab would have 12 Months Parts & Labour Warranty
a New Combilift would have 24 Months Parts & Labour Warranty
a New Mitsubishi would have 36 Months PArts & Labour Warranty

etc

Any "Used" or "Refurbished" machine from the text boxes, regardless of "Make" would be 3 months parts and labour.


I just can't figure it out and have been looking at other threads for 2 hours trying various options - nothing seems to work.

 

[Yongle]

To understand the method, follow these instrutions in a test workbook and then adapt to your own requirements

1. open a NEW workbook, insert a new sheet and paste values below into Sheet2 in cells A1:B4

[TABLE="width: 150"]
<tbody>[TR]
[TD]Models[/TD]
[TD]Warranty[/TD]
[/TR]
[TR]
[TD]Cesab[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]Combilift[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]Mitsubishi[/TD]
[TD="align: right"]36[/TD]
[/TR]
</tbody>[/TABLE]

2. insert a (Form Control) Check Box in cell B18

3. right-click on Check Box \ Format Control \ cell link A18 \ OK

4. formula in Sheet1 cell C22 =IF($A$18=TRUE,VLOOKUP(B22,Sheet2!$A$2:$B$4,2,0),3)

5. select Sheet1 B22 \ click on Data tab \ Data Validation \ select List \ source formula =Sheet2!$A$2:$A$4 \ OK

6. select different values from dropdown in B22 and check\uncheck the Check Box in B18 to test formula

Consider
- creating a named range for your lookup table
- are parts and labour warranties ALWAYS identical? (they are in your explanation but is this always true for all makes?)

Note
- the formula requires a linked cell to determine check box value