Forum Discussion

jrayluv's avatar
jrayluv
Copper Contributor
May 16, 2022

INDEX MATCH function, avoid duplicate returns + with criteria

please help me, I am stuck with this formula.

I am using index to lookup many Sales Name to a table.

but sometimes have someone work-off (column H - Data sheet)

so how make the Name Sales work-off do not display in table ?

thanks for help.

 

this my file

https://www.mediafire.com/file/n0jpwav8dqsdbni/Sales.xlsm/file

 

Formulas and Functions

3 Replies

  • Brad Yundt's avatar
    Brad Yundt
    MVP
    May 16, 2022
    You can hide values in H10:H45 using Conditional Formatting formula:
    =AND(ISNUMBER(H10),J10<>"",H10>DATE(RIGHT(J10,4),MID(J10,4,2),LEFT(J10,2)))

    If this formula returns TRUE, apply a Custom Number Format ;;;
    • jrayluv's avatar
      jrayluv
      Copper Contributor
      May 16, 2022

      thanks for reply.
      I want to explain:
      - My data on sheet Data (pic 1)
      - I want to lookup Sale Name from data to table on sheet 1,2,3...(pic 2) (this number from 1-31 => days of month). Sometimes some person work-off on a day of month - example 10/5/2022.
      So how can I make the Sale Name display on sheet 1-9, and do not display on sheet 10-31.
      Thanks for help again Brad!
      Your formula make hide data's Sale Name, it will make my sheet from 1-9 do not display the Sales Name.

Resources