Excel MAtching Data
Solved/Closed
Related:
- Excel MAtching Data
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- Excel date format dd.mm.yyyy - Guide
- Excel free download - Download - Spreadsheets
- Number to words in excel - Guide
2 responses
Oterwill
Posts
5
Registration date
Tuesday February 24, 2009
Status
Member
Last seen
February 25, 2009
19
Feb 25, 2009 at 06:32 AM
Feb 25, 2009 at 06:32 AM
The simplest way is to put the Match formula next to your list of all members. Use the column right next to the all list.
=MATCH(Lookup_Value,Lookup_array,[Match_Type])
As your lookup value, use your member from the All Members list. As your Lookup array, use the paid list. The result will be a number of the row in your lookup array, anything with a #N/A will not have paid. Copy down the whole All member list. Make sure that if you do this, your Lookup_array is absolute valued by either shooseing the entire column, or using dollar signs. (E:E or $E:$E) as in my example below.
The resulsts will have an #N/A nest to those who havnt paid. You can then make it more fancy my using an IF(ISERROR formula which you can tell Excel to tell you if someone has paid or not...see below.
IF(ISERROR(MATCH(B4,E:E,0)),"Not Paid","Paid")
B C D E
All List Paid List
4 Member 1 Not Paid Member 3
5 Member 2 Not Paid Member 6
6 Member 3 Paid Member 9
7 Member 4 Not Paid
8 Member 5 Not Paid
9 Member 6 Paid
10 Member 7 Not Paid
11 Member 8 Not Paid
12 Member 9 Paid
13 Member 10Not Paid
=MATCH(Lookup_Value,Lookup_array,[Match_Type])
As your lookup value, use your member from the All Members list. As your Lookup array, use the paid list. The result will be a number of the row in your lookup array, anything with a #N/A will not have paid. Copy down the whole All member list. Make sure that if you do this, your Lookup_array is absolute valued by either shooseing the entire column, or using dollar signs. (E:E or $E:$E) as in my example below.
The resulsts will have an #N/A nest to those who havnt paid. You can then make it more fancy my using an IF(ISERROR formula which you can tell Excel to tell you if someone has paid or not...see below.
IF(ISERROR(MATCH(B4,E:E,0)),"Not Paid","Paid")
B C D E
All List Paid List
4 Member 1 Not Paid Member 3
5 Member 2 Not Paid Member 6
6 Member 3 Paid Member 9
7 Member 4 Not Paid
8 Member 5 Not Paid
9 Member 6 Paid
10 Member 7 Not Paid
11 Member 8 Not Paid
12 Member 9 Paid
13 Member 10Not Paid
Oterwill
Posts
5
Registration date
Tuesday February 24, 2009
Status
Member
Last seen
February 25, 2009
19
Feb 25, 2009 at 06:37 AM
Feb 25, 2009 at 06:37 AM
Sorry: my Table didn't come out...Should look Like this: B C D E All List Paid List 4 Member 1 Not Paid Member 3 5 Member 2 Not Paid Member 6 6 Member 3 Paid Member 9 7 Member 4 Not Paid 8 Member 5 Not Paid 9 Member 6 Paid 10 Member 7 Not Paid 11 Member 8 Not Paid 12 Member 9 Paid 13 Member 10Not Paid
