Index Match from Table AND Date

**djc123** · 03-17-2023, 04:43 AM

Hi there,

I already have a formula that searches a table for a match and then returns a value from another column, on the same row. I would also like the formula to match the date in another column.

Notes for result:
- Formula is for yellow cells in column E.
- This formula currently searches range in columns M-V and returns the Cost of Sale in column I.
- I'd like the month in column X to also match in the formula (I have added the month to column A as a potential helper).

Essentially, I am trying to capture the Cost of Sale per unit for a given month.

Desired Result:
- Avocado January (cell E4) should return 3.26 (from I2)
- Avocado February (cell E14) should return 3.34 (from I10)

I'd really appreciate any help

Many thanks,

Danny

**Glenn Kennedy** · 03-17-2023, 05:05 AM

One way:

=FILTER($I$2:$I$20,(ISNUMBER(SEARCH(C4,$K$2:$K$20)))*(MONTH($X$2:$X$20)=A4))

copied down. See file.

**djc123** · 03-17-2023, 06:28 AM

Hi Glenn,

Many thanks for the reply!

When I transfer this formula over to my larger sheet, I'm having an issue with the "ISNUMBER(SEARCH" part of the formula as we have some product identifiers with matching characters but extra numbers at the beginning or end.

For example: 10010001 and 910010001.

Have you got any suggestions?

Danny

**djc123** · 03-17-2023, 06:35 AM

A solution I found was to add a concatenate formula to the combined PLUs cell with " " at the beginning and end, and then add this same setup to cell that is searched.

Perhaps you have a cleaner version.

Danny

**Glenn Kennedy** · 03-17-2023, 10:06 AM

Are the extra characters in the column C terms or the column K terms?

**djc123** · 03-17-2023, 11:43 AM

I added to both so there's always a blank character at the beginning and end.

**Glenn Kennedy** · 03-17-2023, 11:55 AM

Post a few examples of EXACTLY what you mean by extra characters (in your sample sheet)

**djc123** · 03-17-2023, 10:32 PM

In the example sheet column K has pasted values, but these will be collected from a report I download. I have added the 'source' to AA for demonstration purposes. Column K concatenates a space before and after:

Source - [10070003 | 00030005 | 60040005]
Result - [ 10070003 | 100030005 | 60040005 ]

I do the same for the Menu Items PLU in column C.

This way the "ISNUMBER(SEARCH" of the formula will need to find an exact match and won't have the same problem as before (with PLU's like 10010001 and 910010001).

I've updated the sheet for your reference.

Perhaps there is another way to search for the exact number without the need for this added step. It works well though

Danny

**Glenn Kennedy** · 03-18-2023, 03:37 AM

OK. I'd dump all the helper columns and adjust my original formula:

=FILTER($I$2:$I$20,(ISNUMBER(SEARCH(C4," "&$K$2:$K$20&" ")))*(MONTH($O$2:$O$20)=A4))

see file.

Index Match from Table AND Date

LinkBack

Thread Tools

Rate This Thread

Display

Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Re: Index Match from Table AND Date

Thread Information

Users Browsing this Thread

Similar Threads

Use index and match to summaraze a date column onto another table

Return earliest/latest date/time to utilize in pivot table If/Index/Match/MIN Functions

vlookup or index match with multiple conditions (Date closest match) in pivot table

Using VLOOKUP or INDEX/MATCH to pull values from a table that match any one of a list

[SOLVED] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date

Index Match Match Formula DOESN'T WORK WITH TABLES/TABLE NAMES ??

[SOLVED] INDEX date that MATCH on date range, MATCH code(string)

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions