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

[James84]

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

 

[James84]

here is a link to assist

https://www.dropbox.com/ow/msft/edit/home/Excel/ExcelDistance.xlsx?force_role=personal&hpt_click_ts=1559796776228

 

[sandy666]

your file is NOT shared

 

[James84]

Here is a link to view file example...
https://www.dropbox.com/s/34ugq40vwsxv3q7/ExcelDistance.xlsx?dl=0

 

[sandy666]

maybe try PowerQuery (Get&Transform) then PivotTable

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td][/td][td]

Distance​

[/td][td]

​

[/td][td]

​

[/td][td][/td][td=bgcolor:#DDEBF7]Position[/td][td=bgcolor:#DDEBF7]Right Forward Pocket[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Position[/td][td=bgcolor:#5B9BD5]Adelaide[/td][td=bgcolor:#5B9BD5]Bellerive[/td][td=bgcolor:#5B9BD5]Carrara[/td][td][/td][td=bgcolor:#DDEBF7]Venue[/td][td=bgcolor:#DDEBF7]Carrara[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Left Forward Pocket[/td][td=bgcolor:#DDEBF7]

15000​

[/td][td=bgcolor:#DDEBF7]

15500​

[/td][td=bgcolor:#DDEBF7]

14000​

[/td][td][/td][td][/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td]Full Forward[/td][td]

12000​

[/td][td]

13000​

[/td][td]

16000​

[/td][td][/td][td=bgcolor:#DDEBF7]Distance[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Right Forward Pocket[/td][td=bgcolor:#DDEBF7]

14000​

[/td][td=bgcolor:#DDEBF7]

12000​

[/td][td=bgcolor:#DDEBF7]

14500​

[/td][td][/td][td]

14500​

[/td][td][/td][/tr]
[/table]

M-code:

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

 

[James84]

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.

 

[sandy666]

try:

=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

=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

 

[sandy666]

here isa link to your example file

 

[James84]

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.

 

[James84]

Amazing!!!
Thank you! your are amazing!

Works perfectly