Find and compare values in a sheet.

[JonesyCC]

Hi Everyone

I need a formula that will look at a entire work sheet find a name, then find a date, and display a string from a cell in the date row.


for example I want it to first find all the rows that has "Jane Doe", then find a specific date from those rows, and then display a value from a cell in that row. Keep in mind that the there will be no consistency with the row data, but the column's are static.

help any idea's?

Attachment 261622

[Fotis1991]

A sample workbook pls?

[FDibbins]

Hi and welcome to the forum
Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you

[FDibbins]

self-deleted duplicate post

[JonesyCC]

Hi guys

I found my answer with indexing and matching functions.

=INDEX(D3:D23,MATCH(AK3&AL3,B3:B23&C3:C23,0))

The formula explained
=Index(“Range of cell the Absent will appear in”,Match(“Employee Name”&”Date”,”Employee Name range”&”Date range”,0))

Before you finish your formula you need to make it a Array Formula. To do this once you enter in your formula, you hold the Ctrl & Shift keys down and press enter. You will know you have do it correctly when your formula looks like the one below with the braces on the outside of the formula.

{=Index(D3:D23,Match(G3&H3,B3:B23&C3:C23,0))}

[FDibbins]

Hi and welcom to the forum

Very nice solution

an observation...if you add a helper column and combine B&C, then you can do away with the array and just use a regular formula. Nothing wrong with an array formula, in fact sometimes its all that will work, but if you have a large workbook and use tons of array formulas, it can tend to slow things down

Also, Im not sure if you will be copying this, but if so, you may want to consider absoluting the ranges so they dont change as you copy.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. Please use the Thread Tools (located above your first post) and choose "Mark this thread as solved".
Thanks.