# Excel

#### chrissytan

##### New Member
I have different items that different suppliers can supply I need to excel to calculate the cheapest price. Each supplier has an invoice price then next to it a rebated price.
I can work out the cheapest Rebated price with the formula =Min(B7:AA7)
but to work out a formula to calculate the corresponding invoice price I have in the past used the formula
=IF(AB7=C7,B7,(IF(AB7=E7,D7,(IF(AB7=G7,F7,(IF(AB7=I7,H7,(IF(AB7=K7,J7,(IF(AB7=M7,L7,(IF(AB7=O7,N7,(IF(AB7=Q7,P7,(IF(AB7=S7,R7,(IF(AB7=U7,T7,(IF(AB7=W7,V7,(IF(AB7=Y7,X7,(IF(AB7=AA7,Z7,"")))))))))))))))))))))))))
in the past it has worked but with this particular spreadsheet it seems to have too many cells to calculate as when I try and put this formula in an Error comes up
"specified formula cannot be entered as it uses more levels of nesting than are allowed in current file format"
What format should I save to or how can overcome this problem.
if anyone can understand my ramblings ### Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

#### Gerald Higgins

##### Well-known Member
In older versions of Excel, such as 2003, you are limited to 7 nested IF statements, and your formula has many more.

There are other ways of doing what I think you are doing.

First, a question.
Is it POSSIBLE that AB7 could equal B7, or D7, or G7, and so on ?
If that's NOT possible, then it looks like you are basically looking for the first match to AB7 in alternate columns, and returning the value of the cell to the left of wherever that match is found, and this will be achievable using something like INDEX and MATCH.

#### chrissytan

##### New Member
Thanks as I am not excel savvy at all, could you suggest how I would use Match or Index to calculate this.
Thanks.

#### tfaulkes

##### Board Regular
Hey Chris,

You could use something like this:

OFFSET(A7,0,MATCH(AB7,C7:AA7,0))

#### Dryver14

##### Well-known Member
a bit late but also

=INDEX(C7:AA7,MATCH(\$AB\$7,C7:AA7)-1)

Replies
18
Views
554
Replies
0
Views
56
Replies
1
Views
119
Replies
8
Views
711
Replies
23
Views
875