• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 312
  • Last Modified:

Invoice date formula

Need the highlighted yellow section updated with the "date" in the Raw Data tab as to when the MS# will be invoiced.

Please let me know if you have any questions or concerns.

Pete
27835685-Test-File.xlsx
0
SPXHT
Asked:
SPXHT
  • 2
1 Solution
 
barry houdiniCommented:
Try this formula in C2 copied down

=IFERROR(LOOKUP(9.99E+307,INDEX('Raw Data'!D$3:AZ$200,MATCH(B2&"",'Raw Data'!A$3:A$200,0),0),'Raw Data'!D$1:AZ$1),"No date")

see attached

regards, barry
dates.xlsx
0
 
ScriptAddictCommented:
Berry could you explain the 9.99E+307?  what's up with that value?
0
 
barry houdiniCommented:
That's almost the largest number you can put in a cell in Excel (technically it's 9.99999999999999E+307 but my version should suffice and it's a little shorter). The INDEX and MATCH parts of the formula find the correct row then the LOOKUP function looks for that "Bignum" - it won't find that number so it then matches with the last number in that row, which is your invoice amount, and the date is found from the corresponding column in row 1

regards, barry
0
 
SPXHTAuthor Commented:
That works!

Thanks,

Pete
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now