Formula Request (if possible) - VLookup or Index/Match using a criteria and ignoring blanks

[The Great SrH]

	A	B	C	D	E
1	Joe Bloggs	1.2
2	Your Manager	Job Level	Team Morale	My Morale	Development
3	Joe Bloggs	1.2	This comment is for Option 1	Just for visual	Just for visual
4	Betty Sue	1.2	This comment is a dummy		Just for visual
5	Joe Bloggs	1.2		Just for visual	Just for visual
6	Joe Bloggs	1.3	This comment is for Option 1 & 2	Just for visual

<tbody>
</tbody>

Hi all,


I don't think I'll be able to do this using formulas, but if possible - I'd like to!


I'm looking to do some sort of Index/Match or VLookup to return comments from a table, ignoring blanks and only if the criteria is met.


In A1 will be a drop down list with the managers names. (B1 will also be a drop-down list for Job levels but this is for Option 2)


The formula I am asking for will be in a second sheet, and will be copied down around 20 rows (hoping to return the next comment from range).


This is a two part question:

Option 1:


Lets say in my second sheet the formulas start in A1, I would like the return values to be:

A1 - This comment is for Option 1
A2 - This comment is for Option 1 & 2


The reasons for the return are that it has matched whatever the value is in A1 (Joe Bloggs), and it has searched Column C3-C6 to find the comments that matched the criteria.


Option 2:

This basically needs to be the same search but with an additional criteria. This time we want to check Column B (Job Level) of the table to match what's in B1.


So the only return from my preview in the second sheet would be:
A1 - This comment is for Option 1 & 2


I hope this is clear.


Any help will be greatly appreciated.

 

[Peter_SSs]

Assuming the data shown is in 'Sheet1' then for

Option 1 put this formula in cell A1 of the second sheet and copy down after adjusting the Sheet1 ranges if required.

Excel Workbook
	A
1	This comment is for Option 1
2	This comment is for Option 1 & 2
3
Sheet2

Option 2

Excel Workbook
	A
1	This comment is for Option 1
2
3
Sheet3

 

[The Great SrH]

Hi Peter,

Thanks so much for responding as always!


In the Aggregate part of the formula, what does "15,6" mean?


My data tab is slightly different to what I provided in my example, so i'm just trying to learn how to do it the way I need it to work!


Thanks again

 

[Peter_SSs]

Best not to fully quote long posts as it makes the thread harder to read/navigate. If you want to quote, quote small, relevant parts only. I've actually removed the quote from your post as you didn't refer to it anyway.

In the Aggregate part of the formula, what does "15,6" mean?

15 is like the SMALL function. I'm looking for the smallest row number that meets the conditions, then the second smallest row number ...

6 means to ignore errors. Errors are (deliberately) produced by the division in the function for rows that do not meet the conditions.

Refer: https://support.office.com/en-us/article/aggregate-function-43b9278e-6aa7-4f17-92b6-e19993fa26df