Excel Closed

Question

Acc Code .....    Date..................   Amount....... Cleared
C287974......    31-Jan-13..........   500............. Yes
J320728.......    07-Apr-13..........   2000........... No
1000692......    07-Apr-13..........   3000........... Yes
D346671......    06-Feb-13.........    850............. No

1) I need to get the entire line by using the Acc Code.
If acc code is identified, then the date, amount, and cleared should be shown...
and
2)  i need lookup the same after satisfying one condition like.. if d2=yes, then copy the entire line orelse NA,false wtevr, Please help

Mazzaropi · Answer

Muniz Reza, Good morning.

Your scenario:

.......... A .................. B ................ C .............. D 
1... Acc Code ......... Date.......... Amount.... Cleared
2... C287974...... 31-Jan-13.......... 500........ Yes
3... J320728....... 07-Apr-13........ 2000......... No
4... 1000692....... 07-Apr-13....... 3000........ Yes
5... D346671...... 06-Feb-13......... 850......... No 

Try to use:

F3 = INPUT desired Acc Code

G3 = VLOOKUP(F3,A2:D5,2,0) --> to show Date
H3 = VLOOKUP(F3,A2:D5,3,0) --> to show Amount
I3   = VLOOKUP(F3,A2:D5,4,0) --> to show Cleared

Tell us if it worked for you.

I hope it helps you.

Mazzaropi · Answer

Dear Trowa, Good afternoon. 

Well, you're right if you want to search for an approximated value (in case the exact value is not found) in your job. In this case the parameter Range_Lookup must be completed with 1 or True or not filled.

But if you want to search for the exact value in your data, the left column can be in any order.
In this case simply fill out the Range_Lookup with 0 (zero) or False.

Syntax:
VLOOKUP(lookup_value , table_array , col_index_num , range_lookup)

Greetings from Brazil.

Excel

2 responses

Similar discussions

Subscribe To Our Newsletter!