If function with many, many conditions

I am trying to take what in Sheet1 Cell C2 and move the data to match from Sheet 2. So if I put on $132,000 it would use the column of data on Sheet Two for 100,000 - 150,000 if I put in $31,560 it would use the column of data for $30,000 to $39,999.

Here is the formula I have so far. Tell me where I messed up!

=IF($C$2>="15,000",Sheet2!D4,IF($C$2<="$30,000",Sheet2!E4,IF($C$2<="$40,000",Sheet2!F4,IF($C$2<="$50,000",Sheet2!G4,IF($C$2<="$70,000",Sheet2!H4,IF($C$2<="$100,000",Sheet2!I4,IF($C$2<="$150,000",Sheet2!J4,IF($C$2<"200,000",Sheet2!K4,Sheet2!C4))))))))

Can someone re-write the formula correctly for me?

Se if lookup works for you.

Formula:

Please Login or Register  to view this content.

Start in C5 and drag down.

just an FYI, the formula you gave in post #1...
=IF($C$2>="15,000",Sheet2!D4,IF($C$2< ="$30,000",Sheet2!E4,IF($C$2< ="$40,000",Sheet2!F4,IF($C$2< ="$50,000",Sheet2!G4,IF($C$2< ="$70,000",Sheet2!H4,IF($C$2< ="$100,000",Sheet2!I4,IF($C$2< ="$150,000",Sheet2!J4,IF($C$2< "200,000",Sheet2!K4,Sheet2!C4))))))))
would not work because of a couple reasons.

The reason is that commas have a special purpose in a formula as do dollar signs and quotes. The quotes around the dollar amounts are causing the formula to consider those amounts text but you are referencing a number so the quotes mean the formula will not recognize the number and compare it to the text. The dollar sign is usually used to lock in a cell in some way, in your formula you have the dollar sign, I'm assuming because you have the cell C2 formatted to look like currency but if you click on C2 it only shows a number, no dollar sign. The commas in the dollar amounts in your formula are making excel think the 200,000 the part after the comma is the false part of the if then statement.

This should be how that formula should look...
=IF($C$2>=15000,Sheet2!D4,IF($C$2< =30000,Sheet2!E4,IF($C$2< =40000,Sheet2!F4,IF($C$2< =50000,Sheet2!G4,IF($C$2< =70000,Sheet2!H4,IF($C$2< =100000,Sheet2!I4,IF($C$2< =150000,Sheet2!J4,IF($C$2< 200000,Sheet2!K4,Sheet2!C4))))))))

Next, your note to Dave about not handling 200000 or more and that in cell 13, I do not see any 200,000 value in either sheet for any cell in row 13. If I put in 200001 in C2 of CES tab all the values in the CES tab update. Can you give more information about how and where the formula fails?

I would correct the layout on both sheets to only use 1 column for the left hand categories, then resort to a simple INDEX/MATCH/MATCH:

=INDEX(Sheet2!$A$1:$J$34, MATCH($A5, Sheet2!$A$1:$A$34,0), MATCH(CES!$B$2, Sheet2!$A$1:$J$1,1))