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: 15

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,693
Members
448,293
Latest member
jin kazuya

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