Excel VBA - problem with formula

[krù]

Ciao again!
I have another BIG problem. I have two different worksheets: the firs
one, called INPUT DATA, has model cars in rows and all the variables i
columns; in the second worksheet, called ANALYSIS I put same variable
in rows and years (from 1 to 5) in column.

In ANALYSIS worksheet, I should have a combo box from which I ca
choose the car model; once I did this, excel should recall data fro
INPUT DATA worksheet (that are in rows) and it should put them in th
column "year 1" of ANALYSIS worksheet.

I wrote this formula:

=IF(I4=INPUT DATA!C8,CHOOSE(INPUT DATA!E8),IF(I4=INPU
DATA!C9,CHOOSE(INPUT DATA!E9),IF (I4=INPUT DATA!C10,CHOOSE(INPU
DATA!E10))))

But there is an error!!

Where: I4 is the cell in which I have the combo box list with all ca
models; C8, C9, C10 are the cells in which I have car models in rows
E8, E9, E10 are the cells in which I have the value that correspond t
variables.

Another question: how many function can I nest together?

Please reply, if any expert knows the answers!!

Thank you, cia

 

[Bernie Deitrick]

You are using CHOOSE incorrectly:

=IF(I4='INPUT DATA'!C8,'INPUT DATA'!E8,IF(I4='INPUT DATA'!C9, 'INPUT
DATA'!E9,IF (I4='INPUT DATA'!C10,'INPUT
DATA'!E10, "I4 Doesn't equal any"))))

would be how to re-write your formula. You can nest up to 7 levels of IFs.

But the way that you would actually do this is:

=INDEX('INPUT DATA'!E8:E10,MATCH(I4,'INPUT DATA'!C8:C10,FALSE),1)

especially if you have a lot that you want to do:
=INDEX('INPUT DATA'!E8:E100,MATCH(I4,'INPUT DATA'!C8:C100,FALSE),1)

Of course, since your ranges line up well, you could simply use

=VLOOKUP(I4, 'INPUT DATA'!C8:E100, 3, FALSE)

HTH,
Bernie
MS Excel MVP