# Excel Help

4 replies to this topic

Neowinian

• Joined: 15-June 04

Posted 13 September 2012 - 19:02

Hey all,

I'm trying to create an automatic pricing sheet for work. There isn't a specific % of the cost that is charged for maintenance so I have to put all the data in a different sheet and reference too it.

So what I have in the reference sheet is the number of users and then a price to be charged. I need to make a formula in another sheet that looks at this sheet.

in a1 we would enter a number
b1 would be =IF(A1=Sheet1!A1,Sheet1!B1,if(A1=Sheet1!A2,Sheet1!B2,if(A1=Sheet1!A3,Sheet1!B3,.....)

Is there a better way to do this so that I don't need to type out each of the 250 reference lines?

### #2 Kami-

Kami-

♫ d(-_-)b ♫

• Tech Issues Solved: 2
• Joined: 28-July 08
• Location: SandBox

Posted 13 September 2012 - 19:08

Try researching 'VLOOKUP'

Neowinian

• Joined: 15-June 04

Posted 13 September 2012 - 19:24

That will do it. Thank you!

### #4 Kami-

Kami-

♫ d(-_-)b ♫

• Tech Issues Solved: 2
• Joined: 28-July 08
• Location: SandBox

Posted 14 September 2012 - 20:43

No problem

Don't forget if / iserror if you want to replace the no match found error from #N/A

=IF(ISERROR(VLOOKUP( ...

### #5 +S_Herbie

S_Herbie

Neowinian Senior

• Joined: 20-October 03
• Location: Kent, England

Posted 17 September 2012 - 13:22

No problem Don't forget if / iserror if you want to replace the no match found error from #N/A =IF(ISERROR(VLOOKUP( ...

In Excel 2007 onwards you can use "=IFERROR(..." instead.
This will calculate faster when using large arrays in formulas (e.g. VLOOKUP)