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

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
• 2
1 Solution

Commented:
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

Commented:
Berry could you explain the 9.99E+307?  what's up with that value?
0

Commented:
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

Author Commented:
That works!

Thanks,

Pete
0

Featured Post

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