Match Data between 2 Sheets and Transfer Data

**Gingeiko** · 02-06-2012, 02:44 AM

Hi,

I have a Sheet "Form" with Column A (Name), B (Age), C (Blood Type), D (Staff) and E (ID). Data is available in col. A, B, C & D. IF column D shows "Yes", I need to match field A, B & C to another Sheet "Source". If there is a match for all 3, the data ID would be transferred from Sheet "Source" to "Form".

I have attached an example, showing the "Form" Before & After the macro, and the "Source" Sheet.

I have also tried writing a code but doesn't seem to work, any ideas?

Please Login or Register  to view this content.

**TMS** · 02-06-2012, 06:24 AM

You could use this:

=IF(D2="Yes", IFERROR( INDEX(Source!D:D,MATCH($A2&$B2&$C2,Source!$A:$A&Source!$B:$B&Source!$C:$C,0)),"staff-not found"),"not staff")

Committed with Ctrl-Shift-Enter (Array Entered)

That formula specifically identifies the "not found" or "not checked" scenarios.

It is more usual to just have something like:

=IF(D2="Yes", IFERROR( INDEX(Source!D:D,MATCH($A2&$B2&$C2,Source!$A:$A&Source!$B:$B&Source!$C:$C,0)),""),"")

However, as both variants of this formula lookup whole columns, the calculation will be slow.

I would therefore recommend:

=IF($D2="Yes", IFERROR( INDEX(Source!$D$2:$D$6,MATCH($A2&$B2&$C2,Source!$A$2:$A$6&Source!$B$2:$B$6&Source!$C$2:$C$6,0)),"staff-not found"),"not staff")

This is also committed with Ctrl-Shift-Enter but will be much quicker. You will have to adjust the ranges to reflect your data.

It will display as:

{=IF($D2="Yes", IFERROR( INDEX(Source!$D$2:$D$6,MATCH($A2&$B2&$C2,Source!$A$2:$A$6&Source!$B$2:$B$6&Source!$C$2:$C$6,0)),"staff-not found"),"not staff")}

Regards, TMS

holin_168 · 02-14-2012, 11:11 AM

I just wonder why do you need to use VBA code? Since Ms Excel VBA or macro is very harmful so some user may not enabled macro so the VBA code will have problem. VBA code will be useful when Ms Excel formula can't meet requirement or it may to complex.

I have a similar post at Mr. Excel (my personal Excel site) I have a similar post, please go there and have a look. The solution will work with only a short and simple formula vlookup, iferror, and if only. Please see below formula:

IF(F5="Y",IFERROR(VLOOKUP(C5&E5,Source!$A$19:$D$27,4),""),"")

When you look to this formula you can see in Vlook up there is "&" has been used. This is because we need to verify two conditions for vlookup to translate the result. You can use more than two. Please download my file here, then it would be very easy for you to learn from it.

Anyway you will benefit to have a simple way to extract some information from your table based on some condition as well, same post.