Thread: Match data in two columns and import third

I am trying to import the data in col c in sheet 1 into sheet 2 if the remaining two cols match.

For example, item and qty in sheet 2 should match item and qty in sheet 1 and then SR# in col c in sheet 1 needs to be imported into sheet 2 col a.

In the attached sheet I have tried to use match which I guess is wrong as it is returning wrong SR#.

Please help.

Thanks,
ram

Originally Posted by swamram

I am trying to import the data in col c in sheet 1 into sheet 2 if the remaining two cols match.

For example, item and qty in sheet 2 should match item and qty in sheet 1 and then SR# in col c in sheet 1 needs to be imported into sheet 2 col a.

In the attached sheet I have tried to use match which I guess is wrong as it is returning wrong SR#.

Please help.

Thanks,
ram

Try this:

Enter the following formula in sheet 2 cell A4.

=INDEX(Sheet1!$C$2:$C$2493,MATCH(1,IF(Sheet1!$A$2:$A$2493=$B4,IF(Sheet1!$B$2:$B$2493=$C4,1)),0))

Control+Shift+Enter (not just with enter) and copy down.

HTH.

Thanks HTH. it did work. i am trying tounderstand what you have done.

I am a beginner level user of excel and am trying to understand cntrl+shift+enter. If you donot mind, can you tell me what was wrong with the way I had done?

Thanks again for your help,

ram

Originally Posted by swamram

Thanks HTH. it did work. i am trying tounderstand what you have done.

I am a beginner level user of excel and am trying to understand cntrl+shift+enter. If you donot mind, can you tell me what was wrong with the way I had done?

Thanks again for your help,

ram

=IF(AND((MATCH(B4,Sheet1!$A$2:$A$2493,0)),(MATCH(C4,Sheet1!$B$2:$B$2493,0))),VLOOKUP(B4,Sheet1!$A$2: $C$2493,3,0),0)

For my understand.

If found B4 at any cell of range Sheet1!A2:A2493 and found C4 at any cell of range Sheet1!B2:2493 then invoke vlookup statement. If not found both values the results is 0. Value that found in match function in above formula not corresponding row of lookup value. The result of vlookup is the first value that met in your range.

Lookup value by condition can solve by array formula. To enter array formula must be Ctrl+Shift+Enter.

Hope this helps.

Originally Posted by snasui

=IF(AND((MATCH(B4,Sheet1!$A$2:$A$2493,0)),(MATCH(C4,Sheet1!$B$2:$B$2493,0))),VLOOKUP(B4,Sheet1!$A$2: $C$2493,3,0),0)

For my understand.

If found B4 at any cell of range Sheet1!A2:A2493 and found C4 at any cell of range Sheet1!B2:2493 then invoke vlookup statement. If not found both values the results is 0. Value that found in match function in above formula not corresponding row of lookup value. The result of vlookup is the first value that met in your range.

Lookup value by condition can solve by array formula. To enter array formula must be Ctrl+Shift+Enter.

Hope this helps.

Hi, on the back of this what or how can i resovle the following - If one of the "match" cases appears twice, how can i specify which value to return as in...

Match Case 1 - "A" , Match Case 2 "B"

Sheet1:-
Colum1 | Column2
A |A
A |B

I would want to match value "A" in Col. 1 and match value "B" in Col.2 but then pull the value from Col.3 within the same row

I know thats a bit confusing but hopefully someone can follow what i mean. I have tried the above formulas and i just get a returned value of the "first" row that is found that matches the first "match" value.

thanks

Originally Posted by rka81

Hi, on the back of this what or how can i resovle the following - If one of the "match" cases appears twice, how can i specify which value to return as in...

Match Case 1 - "A" , Match Case 2 "B"

Sheet1:-
Colum1 | Column2
A |A
A |B

I would want to match value "A" in Col. 1 and match value "B" in Col.2 but then pull the value from Col.3 within the same row

I know thats a bit confusing but hopefully someone can follow what i mean. I have tried the above formulas and i just get a returned value of the "first" row that is found that matches the first "match" value.

thanks

Try this:

=Index(C1:C5,Match(1,If(A1:A5="A",If(B1:B5="B",1)),0))

Ctrl+Shift+Enter

Good Morning!

I am John Alliage Tinio Morales from GMA News, a leading news broadcast network based in Manila, Philippines.

Currently, I am working on a story about the conditional cash transfer program of my country. As the flagship anti-poverty program of Philippines, the CCT program is a welfare program which provides cash incentives to targeted beneficiaries once they meet the conditionalities on health and education set by the government.

Thus, we ask our concerned government agencies for the breakdown of the social services and public infrastructure in place in towns as well as the list of CCT areas where the program is installed.

We would like to assess if the CCT areas have enough schools, public roads and schools, so that the targeted beneficiaries can meet the conditions set by the government (i.e. children between 0 and 14 age attend schools at least 85 percent every month and mothers have to attend health centers twice a month). We would like to look into the state of public roads in these towns, because the targeted beneficiaries, particularly in far-flung areas, have to receive their month cash allocations in government banks located in urban centers. We also receive reports that poor families who meet government’s requirements for the cash allocation are not enrolled in the program, because government employees fail to come over far-flung areas where there is insufficient, or lack, of public roads leading to these areas.

They provided us the data that we needed such as the list of barangays (our country’s smallest political unit) without elementary schools and health centers and the list of barangays where the CCT program is installed. However, we have difficulties in constructing a database, because the list of barangays without elementary schools and health centers and thee list of barangays where the CCT program is installed does provide a unique geographic numeric code.

We already received from our government’s statistical agency the list of barangays with their corresponding official geographic code.

Our main problem is how we could be to include the geographic code in our data which do not have a geographic code. The data are formatted in excel.

We have already tried your suggestions we found in database/excel fora in the Internet. We tried the match and transfer functions. However, the suggestions did not work.

We hope you could be able to help us. We are attaching the documents.

The excel file named “2011_OFFICIAL_GEOGRAPHIC_CODE” contains the list of barangays with their corresponding unique numeric code. The file does not have the unique geographic code.

The excel file named “4Ps_DATABASE” contains the list of barangays where the CCT program is installed. The file does not have the unique geographic code.

The excel file named “BWOBHS” (or the Barangay Without Barangay Health Centers) contains the list of barangays without health centers. The file does not have the unique geographic code.

The excel file named “BWOES_DATABASE” (or the Barangay Without Elementary Schools) contains the list of barangays without elementary schools. The file does not have the unique geographic code.

Our purpose is to construct a database in MS Access. We understand that MS Access requires a unique primary key in construction relationships between data.

We hope you could accommodate our request as we are airing our report in our primetime news program, 24 Oras (24 Hours), later this week.


Respectfully yours,

John Alliage Tinio Morales
Researcher
GMA News