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
 

Some videos you may like

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

Well-known Member
Joined
Oct 24, 2015
Messages
5,314
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

Well-known Member
Joined
Oct 24, 2015
Messages
5,314
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,099,007
Messages
5,465,976
Members
406,458
Latest member
Barboza Babcock

This Week's Hot Topics

Top