Excel

Closed
Report
Posts
6
Registration date
Saturday September 14, 2013
Status
Member
Last seen
November 10, 2013
-
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
-
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
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
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
Posts
1950
Registration date
Monday August 16, 2010
Status
Contributor
Last seen
January 9, 2022
148
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
Posts
2847
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
January 13, 2022
491
Thanks for clarifying Mazzaropi, didn't know there was a differentiation.

Greetings from Holland.
0