Excel

[chrissytan]

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

 

[Gerald Higgins]

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]

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

 

[tfaulkes]

Hey Chris,

You could use something like this:

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

 

[Dryver14]

a bit late but also

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