User input to display and $ value

  • Thread starter Thread starter Greg Bruner
  • Start date Start date
G

Greg Bruner

I have a list of all of are equipment with a price per
hour for that equipment. Is there a way that I can enter
the equipment number and have the price show up in the
next cell. I tried a IF(AND(22=10.00),IF(AND(23=10.00),IF
(AND(24=50.00)))), but we have to many pieces of
equipmnet. Excel tells me that my formula is to long.
Also I read that an IF statement can only have so many
arguments. Can someone help me.

Thanks,
Greg
 
Hi Greg
I would suggest to do the following
1. Create a equipment/price list on a separate sheets (e.g.
'Sheet_Equipments'). In column A you store the equipment number and in
column B the respective price
2. If you enter an equipment number on sheet1 (assumption column A)
enter the following formula in column B (cell B1)
=VLOOKUP(A1,'Sheet_Equipments'!$A$1:$B$99,2,0)
you can copy this formula for the entire column B

If you like, you can add some error checking:
=IF(A1<>"",IF(ISNA(VLOOKUP(A1,'Sheet_Equipments'!$A$1:$B$99,2,0)),"equi
pment-ID not found",VLOOKUP(A1,'Sheet_Equipments'!$A$1:$B$99,2,0)),"")

HTH
Frank
 
Hi Greg
Try using VLOOKUP.
Assuming your products are listed in A1:A100 and their respective prices in
B1:B100

If you enter a product number in say C1, then in cell D1 enter
=VLOOKUP(C1,$A$1:$B$100,2,0)
to have the price show up in cell D1
 
Back
Top