Find a specific date within a table and return value is specific column in the same row

**NatalieCox** · 03-15-2023, 01:13 PM

Hello,

I am trying to avoid multiple if, and statements in a project I am doing. What I have is a table of dates in C2:Q31. I am looking for a formula that would find a specified date within the table and return the date in column Q within the row the date is found. I tried VLookup and is only looking for the date in column C and not columns D through P. Any suggestions would be very much appreciated.

Thank you!

**XLent** · 03-15-2023, 01:53 PM

you may need to post a sample file to better illustrate the requirement

the below would return the first row (number) in which a given date (A1) is found in the range specified

=AGGREGATE(15,6,ROW(2:31)/(C2:Q31=A1),1)

however, you mention you want to find the date in Q so not entirely clear....at least, not to me

**NatalieCox** · 03-15-2023, 03:02 PM

Thank you for your reply. I have added a sample workbook. The ultimate goal is to add the pay date in column P based on the date in column C. The pay periods/pay dates are in the payroll periods sheet and the pay date is located in column Q of that sheet. I am working on an if, and formula, but it is taking forever as there is a typo I am trying to locate. I am convinced there is a better way. Any help is appreciated.

**JohnTopley** · 03-15-2023, 03:16 PM

If I understand correctly ..

try

Formula:

Please Login or Register  to view this content.

**NatalieCox** · 03-17-2023, 04:07 PM

Thank you for your reply. Unfortunately, this did not work.

**JohnTopley** · 03-17-2023, 04:24 PM

Try

=INDEX($AB$2:$AB$31,MATCH($C2,$Z$2:$Z$31,1))

If this not correct then please add expected results to your file,

**FlameRetired** · 03-17-2023, 07:20 PM

If I understand correctly try this in P2 and copy down until you get blanks. Although I am somewhat confused by the instructions for testing each date in Sample!Column C and the formula that was text entered in Sample!P2 that only tests the first date in Sample!Column C (ie.C2).

This tests all those dates in column C.

Formula:

Please Login or Register  to view this content.

**torachan** · 03-17-2023, 07:31 PM

A cursory glance at both sheets shows a mix of US and UK formatting - is this intentional or is it just my locale messing up the sheets.

**Gregb11** · 03-17-2023, 10:37 PM

Try this in P2 of "Sample" sheet:

=INDEX(PayrollPeriods!$Q$2:$Q$31,XMATCH(C2,PayrollPeriods!$C$2:$C$31,-1))

and copy down.

**FlameRetired** · 03-19-2023, 03:09 PM

This I found to calculate much faster (about 25% less time for the calculated range) than my previous formula in post #7. It takes advantage of the LAMBDA function(s) in Office 365. In P2 and copied down.

Formula:

Please Login or Register  to view this content.

**NatalieCox** · 03-20-2023, 02:16 PM

FlameRetired,

That worked, thank you!

**FlameRetired** · 03-20-2023, 02:23 PM

You are welcome. BTW: which of my two formulas are you referring to?

**NatalieCox** · 03-20-2023, 02:25 PM

This one: =IF(C2="","",INDEX(PayrollPeriods!$Q$2:$Q$31,MATCH(1,MMULT(--(C2=PayrollPeriods!$C$2:$P$31),TRANSPOSE(COLUMN($C$2:$P$31))^0),0)))

**FlameRetired** · 03-20-2023, 02:30 PM

Thank you. Glad it helped. Thank you for the feedback and marking your thread Solved.

Find a specific date within a table and return value is specific column in the same row

LinkBack

Thread Tools

Rate This Thread

Display

Find a specific date within a table and return value is specific column in the same row

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Re: Find a specific date within a table and return value is specific column in the same ro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Find a specific number in a large table of numbers and return where it is

[SOLVED] Find duplicate return specific column color

[SOLVED] Find the last specific value in a column and return the adjacent value

[SOLVED] Find specific value in table and return header row and header column value

[SOLVED] Return a specific value for a date from a table of date ranges

Find specific text in column and return cell value from its immediate right

Find specific keywords in a string and return them in respective column.

Bookmarks

Bookmarks

Posting Permissions