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

James84

New Member
Joined
Jul 20, 2014
Messages
9
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
-Adelaide Oval
-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:
=IF(AND(A3="Left Forward Pocket", A7="Adelaide Oval"),E3,"")
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
List Box 2=Adelaide Oval
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
List Box 2=Adelaide Oval
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

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

I've tried to add additional IF AND statements, but the cell reads #VALUE !

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
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

maybe try PowerQuery (Get&Transform) then PivotTable

Distance​
PositionRight Forward Pocket
PositionAdelaideBelleriveCarraraVenueCarrara
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
Joined
Jul 20, 2014
Messages
9
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
Joined
Oct 24, 2015
Messages
7,497

ADVERTISEMENT

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
Joined
Jul 20, 2014
Messages
9
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.:confused:
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.
 

Watch MrExcel Video

Forum statistics

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

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