Formula help

[danushka]

Dear friends.I'm going to make excel base cheque printing format.

I need formula to auto file "C6" & "L8" cells.(pay name & amount) .if i change "C1" cell number ,base on column "A" that two cells need to auto file.pls give me formula for this.thanks in advance.

 

[Trebor76]

Hi danushka,

Try this formula for cell C6 (payee)...

=VLOOKUP($C$1,$A$14:$B$16,2,FALSE)

...and this formula for cell L8 (amount):

=VLOOKUP($C$1,$A$14:$C$16,3,FALSE)

Just change the table_array range part of the formulas to suit (if necessary).

HTH

Robert

 

[danushka]

thanks a lot Robert.it's work very well..

 

[Trebor76]

You're welcome

Just bear in mind if there's no corresponding (matching) number from cell C1 the formulas will return "#N/A".

 

[danushka]

You're welcome

Just bear in mind if there's no corresponding (matching) number from cell C1 the formulas will return "#N/A".

If something happened like that ,do u have any other way to keep that cell empty, other than "#N/A".

 

[Aladin Akyurek]

If something happened like that ,do u have any other way to keep that cell empty, other than "#N/A".

Excel 2007 and beyond...

=IFERROR(VLOOKUP(C1,$A$14:$B$16,2,0),"")

On all versions...

=IF(ISNUMBER(MATCH(C1,$A$14:$A$16,0)),VLOOKUP(C1,$A$14:$B$16,2,0),"")

 

[danushka]

thanks a lot.it's work.

 

[Trebor76]

You're welcome and thanks too to Aladin (yet again)