Excel vlookup easy problem? (I can't solve!)

[ssmacc]

Hello All,

I've trying to write a vlookup from another worksheet that will pull through the visit, date and weight given only the name (there are columns between the example above in the real version)

I've no doubt foolishly tried the below and get a #Spill! error (where "VS" is the other workseet and C is the name)

My thought is if the column contains the look up name return the first instance (row), followed by all other rows with the look up name (these are sequential as shown) in order can this be done?

=IFS(A5=VS!C:C,VLOOKUP(A5,VS!C$3:U$200,2,FALSE))



I'm not sure this makes sense!

 

[adolf hamster]

so you want to have a box where you type the name, and it'll give you the list of visits for that person in order?

one way you could do this is to add a filter to the existing table, it'll give you a drop down box where you can then select a name (or indeed, by other criteria, say people who attended on a given date) and it'll display just those values. just highlight the whole table, go to the data tab and click filter.



if you need to have the values be in a list that isn't just hiding cells (say if you wanted to have a dynamic chart of weight over the number of visits) then the following might work:

going roughly by method #1 here: https://www.xelplus.com/return-multiple-match-values-in-excel/

it gets a bit messy but going by your image (assuming the sheet name of the image is "Vs") then the text comes out something like this:


=IFERROR(INDEX(Vs!$A$2:$D$15,AGGREGATE(15,3,(Vs!$A$2:$A$15=Sheet2!$B$1)/(Vs!$A$2:$A$15=Sheet2!$B$1)*ROW(Vs!$A$2:$A$15)-ROW(Vs!$A$1),ROW(A1)),COLUMN(A1)),"")

where:
yellow- the entire table selected (without the headers)
red- the column with the name you want to look up
green- the cell where you want to type the name (basically the search box)
pink- a cell on the header row (just in case your table doesn't start on row 1 as shown)

you can then just drag this across for however many columns you need and down for however many cells will cover the number of visits (eg if people never visit more than 10 times then drag down 10 rows or more) any entries beyond the ones found will just show up as blank.

this will also work if you add new entries, so say jane has another 2 visits that are filled in at the end of the list (after freddy's visits) then as long as the yellow and red selections are large enough to cover the new entries, they'll show up.

however the list won't sort by the visit number, so say jane has her 8th visit entered before her 6th and 7th visits then it'll list as 1,2,3,4,5,8,6,7.

if you want it to also sort by the visit number (ie they aren't all in order on the original table) then you can use another table/worksheet with:

=IFERROR(INDEX(Sheet2!$A$3:$D$17,MATCH(ROW(A1),Sheet2!$B$3:$B$10,0),COLUMN(A1)),"")

where purple is the table of unsorted values (ie the one made using the first formula) and orange is the column with the visit number (which needs to be a number rather than text)

 

[ssmacc]

so you want to have a box where you type the name, and it'll give you the list of visits for that person in order?

one way you could do this is to add a filter to the existing table, it'll give you a drop down box where you can then select a name (or indeed, by other criteria, say people who attended on a given date) and it'll display just those values. just highlight the whole table, go to the data tab and click filter.



if you need to have the values be in a list that isn't just hiding cells (say if you wanted to have a dynamic chart of weight over the number of visits) then the following might work:

going roughly by method #1 here: https://www.xelplus.com/return-multiple-match-values-in-excel/

it gets a bit messy but going by your image (assuming the sheet name of the image is "Vs") then the text comes out something like this:


=IFERROR(INDEX(Vs!$A$2:$D$15,AGGREGATE(15,3,(Vs!$A$2:$A$15=Sheet2!$B$1)/(Vs!$A$2:$A$15=Sheet2!$B$1)*ROW(Vs!$A$2:$A$15)-ROW(Vs!$A$1),ROW(A1)),COLUMN(A1)),"")

where:
yellow- the entire table selected (without the headers)
red- the column with the name you want to look up
green- the cell where you want to type the name (basically the search box)
pink- a cell on the header row (just in case your table doesn't start on row 1 as shown)

you can then just drag this across for however many columns you need and down for however many cells will cover the number of visits (eg if people never visit more than 10 times then drag down 10 rows or more) any entries beyond the ones found will just show up as blank.

this will also work if you add new entries, so say jane has another 2 visits that are filled in at the end of the list (after freddy's visits) then as long as the yellow and red selections are large enough to cover the new entries, they'll show up.

however the list won't sort by the visit number, so say jane has her 8th visit entered before her 6th and 7th visits then it'll list as 1,2,3,4,5,8,6,7.

if you want it to also sort by the visit number (ie they aren't all in order on the original table) then you can use another table/worksheet with:

=IFERROR(INDEX(Sheet2!$A$3:$D$17,MATCH(ROW(A1),Sheet2!$B$3:$B$10,0),COLUMN(A1)),"")

where purple is the table of unsorted values (ie the one made using the first formula) and orange is the column with the visit number (which needs to be a number rather than text)

Thanks very much for your response.

Not had chance to go through it properly yet (other work commitments) but I will.

Thanks again for the detailed response, much appreciated!

 

[Grrrrr]

If you're using the latest version of Excel then try this:

=FILTER($A$2:$D$15,$A$2:$A$15="Jane")

(I'm writing this on my phone so I've not checked it)