MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
I have a spreadsheet with two sheets in it.
Sheet A has a list of names in one column with points assigned to them in another column.
Sheet B has the same list of names and a column for points.
I want sheet B to compare the list of names to Sheet A and if the names match it puts that persons points from Sheet A into the column on Sheet B.
The order of the names in Sheet A and Sheet B can change depending on how I sort them.
I have tried IF, VLOOKUP and INDEX/MATCH functions.
This will save me a lot of effort if I can get it right!
That’s exactly what VLookup is for.
Assuming sheet A has names in Col a and points in Col b, and sheet b has names in Col a.
In sheet b Col b you’d put:
=vlookup(name, range, 2, “FALSE”)
Name is your lookup value (link to the column immediately to the left).
Range is all the cells in sheet a - you probably want $ signs so the reference doesn’t change when you drag it down so it’ll be something like SheetA!$a$2:$b$100
2 because you want the second (points) column
False because it’s always false
Ok so you have
Sheet A
Name | Data Column 1
Sheet B
Name | Data Column 2
Do you want
Sheet B
Name | Data Column 1 | Data Column 2
as in you want to add a new column to sheet B and add data to it if the Names match up?
For this sort of stuff I now use Power Query, it's nuts how powerful it is (to my non IT mind), it's opened up so much to me!
If I've understood your Q right though I'll have a think about how to do it purely with Excel functions.
Edit: yeah what superficial says...
Example date here:
https://docs.google.com/spreadsheets/d/1Ae2P7dGa6doUB3ghLdoTm2pKXTpH7WmW0B23jEdwgrs/edit?usp=sharing
I want the results sheet to sum all the points total from the various races in the appropriate column (the total is just a simple SUM function).
If possible I want to do the same on the time results spreadsheet as well
don't forget
IF([name]="scaredypants",[points]+20,[points]-5)
I put that in all mine
Just be aware that sorting can screw the vlookup
https://exceljet.net/excel-functions/excel-vlookup-function
you could always use a simple pivot table, and create a vlookup over the top of that.
ie racea raceb racec total
boris x x x xx
nigel xx xx xx xxxx
nicola xxx xxxx xxx xx
then create the vlookup range using the columns names rather than selecting rows or a box
Have tested sorting the data and looks like it doesn't affect the output so all is working as I wanted. Thank you all.
@scaredypants - nice try!
don’t forget
IF([name]=”scaredypants”,[points]+20,[points]-5)
Excel functions puns - that's a new level of saddo!
Only surpassed by my good self who got it and chuckled. 🤣
MATCH and INDEX does what VLOOKUP does but is better - more flexible and more robust.
=INDEX(sheetA_data, MATCH(name, sheetA_names, 0))
you surely need the sumif formula. if you have many of one name in sheet a and you want the sum of all of those instances to be returned into one row in sheet b this will be the simplest way of doing it (it also just returns a 0 if there is no match for the name).
=Sumif(criteria range (the names on sheet a), criteria value (the name on sheet b), sum range (the range of values))