I am trying to create a dashboard to show the people on certain medication in aged care. I'm a little stuck when writing the formulas while creating the data sheet. I have a client who does not yet have Excel 2016 so I have to use Vlookup rather than Xlookup. I obviously missed something in either setting up the data or writing the formula as all return an error.
The data I am looking for is in two sheets. The identifier and the section are in the "Lists" tab and the date is in the ""Psychotropic Medication Changes" tab. I note that the formula copied entirely for the whole of the data for section and date, but not for identifier. I'd be interested to know why/how that happened as well if at all possible.
Any assistance with what I have done with the formulas would be appreciated. I need to get this part done before I create the dashboard - although I have started with some pivot tables.
Hi Sue,
To use VLOOKUP the way you need requires a list that has unique values that can be looked up. So in the case of looking up the Section in Col B of the Data sheet, this can work but the table you are looking up data in needs to:
1. have unique identifiers - in this case the 'Identifier'
2. Have this identifier column as the leftmost column in the lookup table
3. The identifier column must be sorted smallest to largest
I've created a table on the Lists sheet (starting in I2) that is set up this way.
Now you can use a VLOOKUP to get the Section for CHEJ on the Data sheet - the result is in B2.
But you can't use VLOOKUP on the data in the 'Psychotropic Medication Changes' sheet because it isn't arranged as it needs to be. There's no unique identifier for each row/record etc (Points 1-3 above).
You need to do some rearranging of your data so that it is in a tabular format and this will allow you to use VLOOKUP an other things.
https://www.myonlinetraininghub.com/vlookup-using-dates
https://www.myonlinetraininghub.com/excel-tabular-data-format
Regards
Phil
Thanks Phil. Most helpful in heading me in the right direction
No worries.
