SOLVED

Select Percentage from a table based on 2 criteria in another table

Copper Contributor

I am working with Office Online (365) and trying to select the percentage from a table based on the total miles and total money earned from another table.

Percentage matrix.png

* miles are in column B, money is listed in row 2

 

Miles and Money.png

* Total Miles are in column E and driver names start on row 20.

 

I found a site that helped me construct this formula:

=INDEX(C6:H12,MATCH(E20,B6:B12,1),MATCH(F20,C4:H4,1))

Unfortunately, the only results returned are #N/A.  

 

What have I missed in creating my formula?  Is there a better way?

3 Replies

@DarrMik 

Change the values in the MILES column to

0

1201

1751

2151

2551

2951

3251

Change the values in the money row to

 

0   5001   6001   7001   8501   9501

 

Use

 

=INDEX($C$6:$H$12,MATCH(E20,$B$6:$B$12),MATCH(F20,$C$4:$H$4))

 

P.S. you mention "money is listed in row 2" but your formula uses C4:H4. One of those is incorrect.

best response confirmed by DarrMik (Copper Contributor)
Solution

@DarrMik Since you mention to be working in the online version for Excel, you can use XMATCH and some dynamic array functionality. Created a mockup of your schedule to demonstrate this.

 

Note that I used named ranges (created in the desktop version) to keep the formulae easier to read. Although you can't create new or change existing named ranges in the online version, you can work with them in Excel for the Web. Replace these named ranges by absolute cell references (e.g. "grid" would become $C$4:$H$10 in the attached example) if you do not have access to Excel for the desktop to change them. Also used some custom formatting for the row and column headers.

@Riny_van_Eekelen 

 

The mock up was very helpful in testing my actual values in a working sheet.  Can't say if this is "The Best Answer" but it does exactly what I want!!

 

Thx for the help

1 best response

Accepted Solutions
best response confirmed by DarrMik (Copper Contributor)
Solution

@DarrMik Since you mention to be working in the online version for Excel, you can use XMATCH and some dynamic array functionality. Created a mockup of your schedule to demonstrate this.

 

Note that I used named ranges (created in the desktop version) to keep the formulae easier to read. Although you can't create new or change existing named ranges in the online version, you can work with them in Excel for the Web. Replace these named ranges by absolute cell references (e.g. "grid" would become $C$4:$H$10 in the attached example) if you do not have access to Excel for the desktop to change them. Also used some custom formatting for the row and column headers.

View solution in original post