Hey everyone,
This is my issue. I have a project where I have to find who provided the minimum cost. When there are two people who tie for minimum cost, I have to show both people on a spreadsheet. Normally, I would just search for the number and manually write it in, but it is getting to the point where I have 60+ people providing costs going 2000 rows down!
I have tried to come up with a formula by using index and offsets, but I cant seem to find the right combination.
My layout on the spreadsheet looks like this:
<tbody>
</tbody>
The #VALUE! is where I need to show who else provided the least cost.
The formula I wrote (which does not work) is: =INDEX($J$2:$P$11,1,OFFSET(J2,,MATCH(A3,J3:P3,0)))
I was trying to write a formula where it finds the first min cost, ignore it, and find the other min cost. I believe I am on the right track, but I cant seem to get it to work.
Any help would be greatly appreciated!!!
This is my issue. I have a project where I have to find who provided the minimum cost. When there are two people who tie for minimum cost, I have to show both people on a spreadsheet. Normally, I would just search for the number and manually write it in, but it is getting to the point where I have 60+ people providing costs going 2000 rows down!
I have tried to come up with a formula by using index and offsets, but I cant seem to find the right combination.
My layout on the spreadsheet looks like this:
| 1st least cost | 1st least cost name | 2nd least cost | 2nd least cost name | Jake | Jim | Al | Mr. White | Jessi | Andi | Carol | |||||
| $1.00 | Al | $1.00 | Al | $44.00 | $11.00 | $1.00 | $2.00 | $87.00 | $1.00 | $45.00 | |||||
| #VALUE! | |||||||||||||||
| $10.00 | Jake | $17.00 | Jim | $10.00 | $17.00 | $65.00 | $58.00 | $46.00 | $59.00 | $77.00 | |||||
| $4.00 | Mr. White | $10.00 | Carol | $72.00 | $37.00 | $78.00 | $4.00 | $97.00 | $82.00 | $10.00 | |||||
| $6.00 | Jim | $8.00 | Al | $97.00 | $6.00 | $8.00 | $20.00 | $8.00 | $94.00 | $9.00 | |||||
| $2.00 | Mr. White | $10.00 | Al | $50.00 | $56.00 | $10.00 | $2.00 | $86.00 | $25.00 | $39.00 | |||||
| $20.00 | Jessi | $40.00 | Mr. White | $86.00 | $75.00 | $48.00 | $40.00 | $20.00 | $71.00 | $98.00 | |||||
| $24.00 | Mr. White | $24.00 | Mr. White | $74.00 | $34.00 | $74.00 | $24.00 | $45.00 | $24.00 | $24.00 | |||||
| $37.00 | Jake | $37.00 | Jake | $37.00 | $37.00 | $100.00 | $74.00 | $100.00 | $53.00 | $38.00 |
<tbody>
</tbody>
The #VALUE! is where I need to show who else provided the least cost.
The formula I wrote (which does not work) is: =INDEX($J$2:$P$11,1,OFFSET(J2,,MATCH(A3,J3:P3,0)))
I was trying to write a formula where it finds the first min cost, ignore it, and find the other min cost. I believe I am on the right track, but I cant seem to get it to work.
Any help would be greatly appreciated!!!
