lookup a value in different arrays with conditions

Ironhan

New Member
Joined
Jul 22, 2020
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

I'm trying to write a worksheet that allows me to look up a value in different arrays with set conditions. (in this case, a tick box.)

Eg. If tickbox A is ticked, lookup on the array A3:B8, if tickbox B is ticked, lookup on the array A10:B15... etc

(Please refer to the image below)

Not sure how should I go about achieving this, any help will be much appreciated.

Thanks !
 

Attachments

  • 12345.jpg
    12345.jpg
    59.2 KB · Views: 11

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

kennypete

Active Member
Joined
Apr 19, 2008
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Here's one way to do it:

1167574.gif


And the code, noting the checkboxes run the associated sub:

VBA Code:
Sub CheckBox5_Click()
    If ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 0
    End If
    Call subCheckboxes
End Sub
Sub CheckBox2_Click()
    If ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 0
    End If
    Call subCheckboxes
End Sub
Sub CheckBox3_Click()
    If ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 0
    End If
    Call subCheckboxes
End Sub
Sub CheckBox4_Click()
    If ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 1 Then
        ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = 0
        ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 0
    End If
    Call subCheckboxes
End Sub


Sub subCheckboxes()
    If ActiveSheet.Shapes("Check Box 5").ControlFormat.Value = 1 Then
        Cells(2, 5).Formula = "=VLOOKUP(E1,A3:B8,2,FALSE)"
        Exit Sub
    ElseIf ActiveSheet.Shapes("Check Box 2").ControlFormat.Value = 1 Then
        Cells(2, 5).Formula = "=VLOOKUP(E1,A10:B15,2,FALSE)"
        Exit Sub
    ElseIf ActiveSheet.Shapes("Check Box 3").ControlFormat.Value = 1 Then
        Cells(2, 5).Formula = "=VLOOKUP(E1,A17:B22,2,FALSE)"
        Exit Sub
    ElseIf ActiveSheet.Shapes("Check Box 4").ControlFormat.Value = 1 Then
        Cells(2, 5).Formula = "=VLOOKUP(E1,A24:B29,2,FALSE)"
        Exit Sub
    End If
End Sub
 

kennypete

Active Member
Joined
Apr 19, 2008
Messages
308
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Did this solution answer your question @Ironhan? Please mark as "Solved" if yes.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
24,095
You can do this with Dependant Named Ranges

In E2 put the formula =VLOOKUP(E1, myRange, 2, FALSE)

Then define the Name:myRange

RefersTo = INDEX($A:$A, MATCH(TRUE, $B:$B, 0) + 1, 1): INDEX($B:$B, MATCH(TRUE, $B:$B, 0) + 6, 1)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,115
Messages
5,768,198
Members
425,459
Latest member
Danniey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top