Formula for copying... only if name is the same

[peri1224]

How to make formula for following situation:

Worksheet1RangeA contains a list of names in A2:A50 with corresponding scores in B2:B50

These values per name should be copied into Worksheet2RangeB, which contains a larger range of names in A2:A100 and scores in B2:B100. The names have a different sequence than Worksheet1.

Is there an IF (or other) formula that can take a scores from Worksheet1, look for the corresponding names in Worksheet2 and copy the Worksheet1 scores into the Worksheet2 column, each for the correct name?

That means every name appearing in Worksheet1 will have a copy operation of the score to its corresponding name in Worksheet2. If a name in Worksheet2 does not appear in Worksheet1, the score colum in Worksheet2 will get 0 or a fixed value, say 25.

Thanks for any suggestions.

[DonkeyOte]

It sounds very much as though you want to look at either

a) SUMIF

b) VLOOKUP

which will depend entirely on how you want to handle missing names on Sheet2

eg

To return 0 for no match

Sheet2!B2: =SUMIF(Sheet1!$A$2:$A$50,$A2,Sheet1!$B$2:$B$50)
copied down to B100

To return 25 for no match

Sheet2!B2: =IF(ISNA(MATCH($A2,Sheet1!$A$2:$A$50,0)),25,VLOOKUP($A2,Sheet1!$A$2:$B$50,2,0))
copied down to B100

In essence you need to think "the other way around", you need to use formulae on Sheet2 to retrieve info. from smaller Sheet1 table as opposed to "copy/push" data from Sheet1 to Sheet2.

[peri1224]

Yes, thank you very much, the 25 formula works if the configuration is as in the example. But it seems not to work if in Sheet2 there are other columns between the name and score columns. Does this only work for adjacent columns? Is there a solution for that?

[DonkeyOte]

Probably but you're going to have to provide far more information... in particular, what are your real ranges ?

[peri1224]

The real ranges in Sheet1 are column B2up for the names, and C2up for the scores. In Sheet2 it's C3up for the names, and J3up for this month's scores. Next month the names remain, but the new scores go into column L3up, etc.
Probably, I will have to make change every month, but that is ok.

[DonkeyOte]

We're heading down a long path here I fear...

Sheet2!J3: =IF(ISNA(MATCH($C3,Sheet1!$B$2:$B$1000,0)),25,VLOOKUP($C3,Sheet1!$B$2:$C$1000,2,0))

The problem you have is next month, if L refer to the same values on Sheet1 then it makes no sense as all months will be returning the same values, ie the values in J3 and L3 will be the same given they will both be returning the same values from Sheet1 ... if you need J to become static then you will need to copy the results (J3 down) and paste special as values over the formulae.

[peri1224]

The scores on Sheet1 change every month. So, yes that would also change the previous months' scores on Sheet2. The formula would have to keep the values intact once they are written into Sheet2. But maybe that is too much to ask for. So I will work around that by copying Sheet1 into a temporary column on Sheet2 (using the exact example configuration), and then copy/paste values from that temp column into the real monthly column. At least I don't have to copy the scores by hand anymore.
Thank you very much.

[peri1224]

New problem. Need to copy also Sheet1 column A2up to Sheet2 column A52up. Thought that this could be handled with the same formula by changing the range, but apparently not. Maybe that is because this time the values are on the left of the column with the names? Is there also a solution for that?

[DonkeyOte]

Maybe that is because this time the values are on the left of the column with the names?

Correct - VLOOKUP can only look left to right and criteria must be in leftmost column of range... for this reason (amongst others) most of us XL-nerds would nearly always opt for INDEX/MATCH over VLOOKUP as it is more flexible though perhaps less intuitive,eg:

Sheet2!?3 =INDEX(Sheet1!$A$2:$A$1000,MATCH($C3,Sheet1!$B$2:$B$1000,0))

[peri1224]

Yes, this works nicely with those that have numbers on Sheet1. But those that are not on Sheet1 are getting an #N/A on Sheet2. Can these get a 25 instead of #N/A?

[DonkeyOte]

Same as before,

Please Login or Register  to view this content.

[peri1224]

Well, to me it's quite different in syntax, and I don't follow anymore in understanding it. But the main thing it works. Thank you very much.

[DonkeyOte]

Same as before in the sense that checking for error and returning 25 is exactly the same as was used in the original VLOOKUP, eg:

=IF(ISNA(MATCH(....)),25,retrieve value)