Want to highlight a helpful answer? Upvote!
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
Did someone help you, or did an answer or User Tip resolve your issue? Upvote by selecting the upvote arrow. Your feedback helps others! Learn more about when to upvote >
HI, how to lookup for a value with multiple result from several columm.
im only able to lookup for the value based on one column but not several column using tis formula based on the table below
IFERROR(DATEVALUE(MINIFS(Leave 2024 Consolidation::Table 1::A,Leave 2024 Consolidation::Table 1::B,Table 1::A$1)),"")
I am assuming that was the formula for the first date and the rest of the column was a different (expanded) formula. That formula will provide only the earliest date that matches the name.
I did it this on one sheet to make it easier to post it here. You can adjust the formulas to match your sheet and table names and the row numbers you are using.
Column F puts all the names into one string with each name having a "~" on both sides.
Table 1::F2 formula ="~"&TEXTJOIN("~",TRUE,B2:E2)&"~"
Fill Down to complete the column
You can hide this column after everything is set up and working
Table 2::A3 =IFERROR(DATEVALUE(MINIFS(Table 1::A,Consolidated,REGEX("~"&$A$1&"~",FALSE))),"")
Table 2::A4 =IFERROR(DATEVALUE(MINIFS(Table 1::A,Consolidated,REGEX("~"&$A$1&"~",FALSE),Table 1::A,">"&A3)),"")
Fill down from A4
MINIFS is looking for "~name in cell A1~" in column F. The "~" on each end is to ensure an exact match for the name. Otherwise, as an example, if you were searching for "Carol" you would also get dates for "Caroline" because it contains "carol".
Lookup with multiple values
