Excel

Closed
Muniz Reza Posts 6 Registration date Saturday September 14, 2013 Status Member Last seen November 10, 2013 - Sep 24, 2013 at 05:45 AM
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 - Sep 26, 2013 at 10:24 AM
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

2 replies

Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Sep 24, 2013 at 09:30 AM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Sep 24, 2013 at 11:53 AM
Hi Guys,

Please note that for VLOOKUP to work without flaw the left most column in the matrix needs to be in alphabetical order.

Best regards,
Trowa
0
Mazzaropi Posts 1978 Registration date Monday August 16, 2010 Status Contributor Last seen November 18, 2022 146
Sep 25, 2013 at 11:45 AM
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.
0
TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Sep 26, 2013 at 10:24 AM
Thanks for clarifying Mazzaropi, didn't know there was a differentiation.

Greetings from Holland.
0