MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Look up


Posted by Joe Gornatti on July 02, 2001 9:31 PM

Hello, Can you help me with a lookup function?
Here is my problem. I created a spreadsheet that I can print my quickbooks check register into. Column D shows the account number that should be debited or credited. Column E shows the amount. All the other columns are the accounts. I need a function I can put in (let's say the Banking expense Column)That can look at column D on that row and if the account numbers match it will put the same amount, in column E, into the Banking exp. Column and row. Thank you. Joe


Posted by Aladin Akyurek on July 03, 2001 12:47 AM

Joe,

It's a bit unclear to me how your data are organized.
Lets say that you have the following accounts and debit/credit amounts in D2:E6.

{"acc1",43;"acc2",-20;"acc3",12;"acc4",27;"acc5",56}

Apparently somewhere on the same worksheet, you have account numbers all in cells of their own.

Say you have in H1 account number "acc2" and you want the debet/credit amount associated with it if any in the range D2:E6 in the cell G1:

In G1 enter: =IF(ISNUMBER(MATCH(H1,$D$2:$D$6,0)),=VLOOKUP(H1,$D$2:$E$6,2,0),"")

This formula returns the amount associated with "acc2" if it exists, otherwise a blank.

Aladin

================