Excel Excel help please

[Ryk]

I am trying to make a vlookup and if statement combined, to me this looks right....

=IF(ISNA(VLOOKUP(Sheet1!$C$4,"no",techs,2)),"",VLOOKUP(A21,techsOT,2))

techs and techsOT are ranges, Sheet1 C4 is a cell I pick yes or no from to switch ranges and A21 is a storage cell that sets the rate for sheet one to multiply times hrs worked? Any ideas on this?

Its basically a plumbing overtime or not scenerio, if yes is clicked, it would look at techs ot range, if no just use his base rate when calculating the bill.

Ryk

 

[SlimJim]

Hi Ryk

Is this formula producing an unexpected result? I'm not sure about the logic of what you are doing but the first VLOOKUP seems to have the wrong number of arguments. The final 2 is I guess the index and that should be the third argument.

 

[Ryk]

Thanks for helping

'Is this formula producing an unexpected result? I'm not sure about the logic of what you are doing but the first VLOOKUP seems to have the wrong number of arguments. The final 2 is I guess the index and that should be the third argument.'

I am new to even using a vlookup, so arguements I do not understand, could you explain arguements? And maybe type my line out the way you think it should go, so I can see the difference?

Thank you

Ryk

 

[SlimJim]

If this is your first time for using VLOOKUP I strongly recommend that you look it up in the Excel help file and work through the examples. It will give you a much better idea of what it does than I can in a short reply.

The normal syntax is
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

where the arguments are separated by commas. The problem with your example lies in the "no" in the second argument which should be a table array and not a string. In addition, outside the VLOOKUP I am not too sure what the ISNA is doing.

I think that some change of the form

=IF(Sheet1!$C$4="no",VLOOKUP(A21,techs,2),VLOOKUP(A21,techsOT,2))

may work. However, without further knowledge of what you are trying to work out, this is just a guess, and A21 that may not be the appropriate lookup value.

 

[Ryk]

Awesome, thank you, that worked beautifully!

Ryk