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

 

[sandy666]

You are welcome

btw. there is a link to non-existing file on your sharepoint remove it first before you share file and don't mark shared files as read only