Jump to content



Photo

Excel Help


  • Please log in to reply
4 replies to this topic

#1 HouseCookie

HouseCookie

    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: 3
  • Joined: 28-July 08
  • Location: SandBox

Posted 13 September 2012 - 19:08

Try researching 'VLOOKUP' ;)

#3 OP HouseCookie

HouseCookie

    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: 3
  • 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)