# Using list boxes to display distance data with multiple IF AND statements

#### James84

##### New Member
This is not about Nested IF statements.

Hi I am trying to output data values when 2 list box selections are true.
ie.
List box 1 = Player position
- Left Forward Pocket
-Full Forward
-Right Forward Pocket
etc...

List box 2 = Venue
-Bellerieve Oval
-Carrara
etc...

When:
List Box 1 is selected as "Left Forward Pocket"
AND
List Box 2 is selected as "Adelaide Oval"

I want to output the total distance (which the data comes from another cell)...i.e 15,000

Now I can do this successfully using the following formula:
Distance=15000

But I would also like to do it with several test cases:
List Box 1=Left Forward Pocket
List Box 2=Bellerive Oval
Distance=15500

List Box 1=Left Forward Pocket
List Box 2=Carrara
Distance=14000

----------------------------------------

List Box 1=Full Forward
Distance=12000

List Box 1=Full Forward
List Box 2=Bellerive Oval
Distance=13000

List Box 1=Full Forward
List Box 2=Carrara
Distance=16000

----------------------------------------

List Box 1=Right Forward Pocket
Distance=14000

List Box 1=Right Forward Pocket
List Box 2=Bellerive Oval
Distance=12000

List Box 1=Right Forward Pocket
List Box 2=Carrara
Distance=14500

----------------------------------------

How can I do this so no matter what options are selected from the list boxes, I can display the distance data I want?

Thanks,
James

### Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

#### sandy666

##### Banned - Rules violations

maybe try PowerQuery (Get&Transform) then PivotTable

 Distance​ ​ ​ Position Right Forward Pocket Position Adelaide Bellerive Carrara Venue Carrara Left Forward Pocket 15000​ 15500​ 14000​ Full Forward 12000​ 13000​ 16000​ Distance Right Forward Pocket 14000​ 12000​ 14500​ 14500​

M-code:
Code:
``````[SIZE=1]// Table1
let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
UnPivot = Table.UnpivotOtherColumns(Source, {"Position"}, "Attribute", "Value")
in
UnPivot[/SIZE]``````

#### James84

##### New Member
I am after a way to display the relevant distance correlated to 'Position' AND 'Venue' so every time a new position/venue is selected from the list box/s, the data shows the relevant distance number. I do not want to display it as a power query or pivot table.
Is there a way to combine IF statements for what i'm after?
The logic I have is that I can just keep adding the necessary code in each cell, but this will not work.

Last edited:

#### sandy666

##### Banned - Rules violations

try:
Code:
``=INDEX(Table1[[Adelaide]:[Carrara]],MATCH(A3,Table1[Position],0),MATCH(A7,Table1[[#Headers],[Adelaide]:[Carrara]],0))``

it works with your table but you need change Venue DataValidation to proper list (headers of the table)

edit:
unstructured formula
Code:
``=INDEX(Sheet1!\$D\$3:\$F\$5,MATCH(A3,Sheet1!\$C\$3:\$C\$5,0),MATCH(A7,Sheet1!\$D\$2:\$F\$2,0))``

better is if table is a ExcelTable because you can add more Positions or Venues and formula will update ranges automatically

Last edited:

#### James84

##### New Member
Hi,

I have been trying your source code, but can't get it to work.
With the data validation for Venue, it is already a list. (I don't understand the layout with the table headers to produce what i'm after.
And yes ideally this would work for all 18 positions on a football oval, but for this example, I am trying to keep it simple.

Amazing!!!

Works perfectly

Replies
4
Views
211
Replies
0
Views
154
Replies
0
Views
240
Replies
3
Views
460
Replies
0
Views
180

1,133,466
Messages
5,658,952
Members
418,478
Latest member

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

### Which adblocker are you using?

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

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