Aug 27 2021 02:25 PM
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.
* miles are in column B, money is listed in row 2
* 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?
Aug 27 2021 02:36 PM
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.
Aug 28 2021 12:01 AM
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.
Aug 30 2021 12:36 PM
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
Aug 28 2021 12:01 AM
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.