Vlookup or formula to return a number related to a word that is typed into a cell

Your choice of data layout is extremely poor and does not lend itself to data lookup and analysis.

You would be far better off having one table for player details (one row or record per player) and another for player stats (one row per player per year). This way the lookups become simple.

Hi Narayan,
Thank you
Almost there. However, I said that the information would not always be in the same set of rows. While the columns will remain constant, the rows will not.
As a result, the data in AA2 does not return the correct value for this new PLAYER 1.
I have switched the data from PLAYER 2 into the location where the data for PLAYER 1 is and vice versa - the data does not display in column AA cell 2 and 3.
Thank you
Doofus

You would be better off reshaping the data pasted from the website into something usable (Get & Transform on the Data ribbon can help with this).

If you follow Narayan's suggestion you are going to need to create and maintain a lot of named ranges. This will not prove practical at all.

But it's up to you ...

As a matter of interest, what is the website? You might be able to import directly from it using Power Query, which would save you all the copying and pasting.

By way of an example of a much more sensible and efficient way to proceed, I am attaching a workbook in which your data has been reshaped using Power Query.

On sheet 2 you can see how to collate the copy and pasted data and on sheet 4 you can see the two reshaped data tables produced by Get & Transform. These can be refreshed (updated) whenever you add more data.

These will then provide a much easier platform for your data extraction and analysis.

Solved in this thread: https://www.excelforum.com/excel-for...ent-cells.html

Members should think twice about wasting their time posting to this thread.

I take it you have no interest in the workbook I prepared for you. I hope it will be of use to someone else.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Hi AliGW,
Sorry I didn't respond earlier.
I do get busy with a bunch of other stuff.
I apologize to you and anyone else I haven't responded to in a timely manner.
Ok. So, the website is http://www.tennislive.co.uk/
On this website, I can access web pages for each of two players who play a match.
The pages will display on either two different tabs or two new windows.
I don't want to flip between the tabs or the windows looking at the player information.
It is easier to copy and paste the relevant data for both Players onto an Excel spreadsheet and then extract the information relevant to me, onto a separate part of that spreadsheet.
However, there are a number of things I need to do - which is why I appear to be doing this piecemeal.
So, for example, in my first posting (which I marked as Solved), I asked how to extract certain information - Name, Age, Wins and Games Played (MatchesTotal).
Once I figured out how to do that, I moved onto the next item - which was to extract the data regarding the win percentages for a specific surface.
However on the spreadsheet I have uploaded here, the full name of the PLAYERS does not appear in column T - in it's entirety - when I delete and enter new data profiles for other players.
So, that party has not gone down too well.
After that, I will need to know how to identify a players best performance surface (without me having to look at the table for each player).

So, for instance, if I type the word "Carpet" into cell S1, in cell Y2 and Y3, it should return the win percentage for each player on that surface. (If like in the case of PLAYER 2, there is no data, cell Y2 should return "N/A".

Then, I would need to identify the best surface on which the player has the best win percentage - Cell Z2, Z3.

Then, I would need to identify the best win percentage figure for that surface - Cell AA2, AA3.

Please note that the row ranges will change for each PLAYER based on how much data is available - so, for example in the uploaded file, PLAYER 2 has very limited data and as a result, there may be issues with scanning the rows and cells related to his information eg 0% may return a DIV or ERROR message etc.

I am still wrestling with the issue of extracting the win percentage for a surface specified by me and trying to figure out why the names are not displaying correctly.
I hope this helps you understand what I am doing.

So, to recap, I will copy the data from any two given Player Profiles on that website (by clicking on the Player's name link) and then paste the info into A2 for Player1 all the way into column H and J2 for Player 2 all the way into column Q.
The data I need will be extracted/returned into the specified cells.
I will type the word that corresponds to the Surface of choice in cell S1 and the win percentages related to that will appear in the designated cell relevant to each player.
I will then need a formula that will scan a Players best win percentage on a surface and enter the relevant Surface in one cell and the win percentage related to that in the cell next to that.

When I am finished with those two Players, I will delete the data profile information, and copy and paste the data profiles for two new players - rinse and repeat.

Also, for you and anyone else, If I don't respond for a while, it is because I am doing something else or not at my computer - would not, intentionally, neglect to respond to anyone.


I have added a new Excel spreadsheet with my doodlings.

Thank you
Doofus.

Which numbers do you need to extract and for what calculations? Tell me exactly what you are trying to do and I can tell you how to do it.

Hi AliGW,
The text and the numbers in the excel spreadsheets I have uploaded.
I needed the name of each Player (in cells T2 and T3),
the age of the respective players (in cells U2 and U3),
the wins if any in cells V2 and V3(if no wins are present the designated cell should return a 0),
the total games played by each Player in W2 and W3,
the win percentage for the surface, that I designate in cell S1, in cells Y2 and Y3,
the surface on which the player earned his/her best figure (that is the text - eg Clay, Grass, Acrylic etc)in cells Z2 and Z3
[if the player has a 0% in all the fields, the cell can return a 0 or an "N/A"],
the percentage earned for that surface in cells AA2 and AA3,
the total Prize money earned - if any [on those web pages,at times, decimal appear to be used instead of comma's] in cells AB2 and AB3.
If the cells can be hot-wired to pick that data up, once I copy and paste the data into the relevant cells in the designated columns - that would send me into chocolate heaven.
Thank you
Doofus

PS: I will check back in around another 10 hours or so.

Have a look at the attached. It's based on what you showed us in post #12, but will give you an idea of what can be done easily with sensible data processing using PowerQuery.

It's up to you ... I'm not going to spend hours working out complex formulae so that you can use your current data layout (someone else might be prepared to) when there's this easy way to do it.

You still haven't (I don't think) given me a link to the website - I wanted to see if we could completely automate this.

[on those web pages,at times, decimal appear to be used instead of comma's]

That's because the tennis world uses UK/US decimal notation, not European.

PS: I will check back in around another 10 hours or so.

OK, but make sure you tell me the address of that website!

When I am finished with those two Players, I will delete the data profile information, and copy and paste the data profiles for two new players - rinse and repeat.

This is a very STUPID idea! All you need to do is have drop-downs to select the players in the results table. I can show you how to do that. You NEVER delete your source data from a file!!! Very, very bad practice.

So it does - I missed that in all the verbosity! Thanks, Narayan.

The good news is that it's really easy to import player match stats into Excel using Power Query directly from the website. I am not sure how to import the player personal details, but I'll put a call out to our PQ expert, Olly.

Here's a query which pulls the data you want, for two specified player names, and a specified surface.

Please Login or Register  to view this content.

I've set it up to get the surface / player names from named ranges on your worksheet, so enter the required values (in the yellow cells) then refresh the data, and it will pull the data you want.

Thanks, Olly - can't wait to have a play with this!

Let's hope Doofus1 will see its worth.

Hi Ali GW,
The website is http://www.tennislive.co.uk/
Hahaha. I know that I am dumber than Gump. But I would clear the contents in columns A through Q, - then copy from the website and paste into the relevant columns/rows on the spreadsheet.
The data I want would be extracted onto the cells in columns U through AB.
I would also enter the surface I want to check on, in cell S1 - and the relevant data would appear in the designated cells.
Thank you

Originally Posted by Doofus1

Thank you Sir.
This is way beyond the mental capabilities of my impaired brain cell.

But it is exactly what you wanted and, indeed, described in post #22, except it needs no copying and pasting.

Now comes the fun part.
Since you are trying to turn me into a Power Query Junkie, give me some time to Google/YouTube Power Queries.
But I have seen the light - on the excel spreadsheet you created - especially the results page.
The question is, how do I bring the data in - on to the Raw Data page - am I copying and pasting or am I missing a great deal?
Thank you
Doofus.

OK, so in Olly's absence:

1. Alter any of the details in cells A1, A2 or A3 (we can make these drop-down selection boxes if you wish).
2. Go to the Data tab and click on the Refresh button - the query will take a moment to refresh and you will see the updated data direct from the website in the table.

If you wanted, we could add a bit of VBA code to have the query refresh each time you change any of the cells A1 to A3, if you wished.

There is no need to download data from the site and this means your data is never out of date.

PS You may need to click on Enable Content in the yellow bar at the top when you open the workbook.
PPS You do not need to delve into the query itself at all.

The fact that you are using 2010 means that you need to download and install the free PowerQuery add-in. Here's how to do that: https://www.excelcampus.com/install-power-query/

Once you have the add-in, you should be good to go.

Please try.

Y2

Please Login or Register  to view this content.

Y3

Please Login or Register  to view this content.

Y3 (avoid header in same row)

Please Login or Register  to view this content.

Regards.

Originally Posted by Doofus1

Thanks AliGW.
Just downloaded and will try again.
Was on a 2019 earlier but a 2010 right now.

Have you changed the details in your forum profile since we started this thread? If so, that is where the confusion has arisen.

That's good!

Have a look at the attached version - I have updated it with drop-down selection lists (using the ATP list from the website). This also updates when you refresh queries.

You missed out the women, Ali

Yes, I did, although they can easily be added from the WTA table if necessary and the lists merged.

Adapting Ali's code a little, to include ATP and WTA top ranked players:

Please Login or Register  to view this content.

I am so glad we have converted you to the wonders of PQ - it really is a Pandora's Box of tricks.

Sweet PQ dreams!!! LOL!

Hi AliGW,
I have more questions.
1. How do you know if a website is Power Query compatible?
Here are two website addresses - from which I'd like to access data onto Power Query tables - like the ones you created.

https://www.nfl.com/standings/conference/2019/REG

https://www.soccerstats.com/leagues.asp

Same website as above, different page
https://www.soccerstats.com/widetabl...league=england


I want to learn how to create the Power Queries for these types of websites - because, being a sports fanatic, this will help me to understand Power Queries.
I will ask questions as I learn to create the queries myself - hope you have the patience to indulge my asinine questions.
This is what happens when you teach children magic tricks.
Thank you

Any chance you could look at it and offer a suggestion(s)?

I suggest you use my last version of the file, which contains drop-down selection boxes. I have updated it to include the WTA list as per Olly's code above and attached it again here.

How do you know if a website is Power Query compatible?

Quickest way is to see what you are offered when you try to import it. I will talk you through how to do this shortly.

OK, so the NFL site will be accessible, but the 2019 and REG bits of the URL control selection boxes on the website, so Olly will need to advise you how to deal with this aspect of the address (they need to be dealt with as parameters). Similarly with the soccer site - because you are specifying choices via the website, these need to be built into the query that you build.

Essentially any website that contains tabular data can be imported (i.e. the data in the table).

I will ask questions as I learn to create the queries myself - hope you have the patience to indulge my asinine questions.

This is a help forum, and no question is stupid if you need to ask it - how else would you learn?

Don't put yourself down. You will learn this, just as I and Olly did, but it may take a while to unlock the full potential. Olly's much further on with it than I am, obviously.

Your forum name, containing 'doof' (stupid in German), seems to be self-deprecating in itself: I always tell my pupils that they CAN do anything.

Originally Posted by Doofus1

However, I have uploaded an Excel file that has a name that does not populate - even after multiple attempts.
Is it because of something I might be doing wrong ? I have copied and typed the name in and then, hit refresh. It still does not populate.

That's because the player page url for "Lloyd Harris" is http://www.tennislive.co.uk/atp/lloy...irhead-harris/ for some reason. So if you enter the name "Lloyd George Muirhead Harris" it will retrieve the details.

There may be a workaround to this, by retrieving the player list first, and associating actual player page URLs with player names, instead of the current assumption that player page urls are all in the format firstname-lastname.

Olly - did you see posts #47 and #49? I was hoping you could refresh my memory on how to handle these web page choices.

Aye. I'll try and have a look later, in between meetings!

I will point out, though, that the NFL terms of service include:
"Systematic retrieval of data or other content from the Services, whether to create or compile, directly or indirectly, a collection, compilation, database or directory, is prohibited absent our express prior written consent."

And the SoccerStats Terms and Conditions of Use include:
"You must not conduct any systematic or automated data collection activities such as scraping, data mining and data extraction on the Website. "

That's a good point, Olly, regarding the T&Cs. Perhaps better not encourage this, then.

I think it might be better to mark it as solved. If you have queries about PQ going forward, you can start a new thread with a suitable title.

How are you expecting this to work? In the PDF example, each of the players is playing on a different surface today, so which of the two do you want to compare and to have appear in A1?

Out of interest, why are you not using the version that I created with the drop-down selectors for the players?

OK - so assuming that only one of the two players is playing today - what then? Getting today's surface is not going to be too difficult, I think, but you need to think this through.

As for my version of the workbook, it only differed in having the drop-down lists - not a massive difference. On reflection, because of the names issue you identified, the drop-down lists won't always work, so it might not be worth bothering with it.

Hi AliGW,
The two players will always play on the same surface.
Because on the website http://www.tennislive.co.uk/ I would only look at the scheduled matches or the finished matches. (the two players selected would have always played against each other on the same surface).
Thus, The two players whose data is extracted on to the spreadsheet would have always played on the same surface.
The problem, for me, would be when I forget to change the surface in cell A1.
When that happens, I may be looking at data for two players for a match on Grass - when it should be on a Hard court.
So, to avoid that mistake, I would like to see how the surface (for today's match or the finished match) for the two players can be extracted onto the spreadsheet automatically to avoid my absent minded negligence.

I will come to your spreadsheets in a little while - they hold a whole different world of possibilities.
For now, I don't want to get ahead of myself - baby steps.
But if it isn't possible that's ok too. I will have to be more mindful while I experiment.
Thank you
Logging off - be back in about 10 hours.

Yes - I understand. I can see in the code on the site where the information is, but I don't know how to extract it. I am sure that Olly will let us know when he calls in here later today.

Where in the world are you? It's breakfast time here.

I see! I'm going to temporarily remove the SOLVED tag here in the hope that Olly spots it.

Here's a pretty rough amendment, which retrieves the "Scheduled / Last" surface for each player. If this is not available, it returns null for Surface and Surface Win %.

Please Login or Register  to view this content.

Am pretty sure I could work out a neater way of getting to the Scheduled / Last Surface, but don't really have much time to look at it today...!

No books (although I do recommend Marco Russo's Definitive Guide to DAX, when you move to Power Pivot) - but I do recommend reading;

• Rob Collie's stuff on PowerPivotPro.com
  
• Imke's amazing work at TheBIccountant.com/
  
• Of course, the official Microsoft documentation: https://docs.microsoft.com/en-us/powerquery-m/
  

Hi Olly,
You created this spreadsheet, some time ago, for me.
This is a Powerquery spreadsheet onto which I could extract data from the website http://www.tennislive.co.uk/
I used to be able to extract data for male and/or female players onto the sheet.
However, it appears to have stopped working for the female players.
It only seems to extract the data for the males.
I wanted to create this to test a hypothesis that female players were as consistent as the males.
Anywho, could you take a look at the M code and tell me why it is no longer picking up the data for the females?
Or offer a fix or solution?
Thank you
Doofus

The issue is completely different to the original thread title.

Please remark this thread as solved, then open a new one with an appropriate PQ related title, and include a link back to this thread.

Thanks.