Excel

[Closed]
Report
Posts
6
Registration date
Saturday September 14, 2013
Status
Member
Last seen
November 10, 2013
-
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
-
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

Posts
1934
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
July 21, 2021
144
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.
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
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
Posts
1934
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
July 21, 2021
144
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.
Posts
2779
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 29, 2021
468
Thanks for clarifying Mazzaropi, didn't know there was a differentiation.

Greetings from Holland.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!