Pick up the relevant rate through formula

**khurramfarooqpk** · 05-03-2013, 11:22 AM

Hi

I have attached the excel sheet and written my problem plz read and find the best solution for me.
thanks in advance

**benishiryo** · 05-03-2013, 11:27 AM

hi there. looks like your formula's working fine? you could shorten it to this though
=VLOOKUP(B10,E3:G6,IF(C10="Staff",2,3),0)*F10

**khurramfarooqpk** · 05-03-2013, 11:52 AM

Thanks a lot its really working good.I want to discuss one point that may happen in future if concessional rate is applicable for 2 categories staff and jobs and market rate for other 2 categories (DS & MS). which formula will be better.

Originally Posted by benishiryo

hi there. looks like your formula's working fine? you could shorten it to this though
=VLOOKUP(B10,E3:G6,IF(C10="Staff",2,3),0)*F10

**benishiryo** · 05-03-2013, 09:04 PM

glad to help. 2 would still be manageable. assuming "Staff" & "VIP" gets concessional rates:
=VLOOKUP(B10,E3:G6,IF(OR(C10={"Staff","VIP"}),2,3),0)*F10

if you get a long list, then you should probably put them in a table with values 2 or 3 beside them. for eg.