LOOKUP formula

[MissingInAction]

Hi everyone.
I have a lookup formula that must search for waybill numbers based on the company that is selected for invoicing. The company name is located in C13 (on the invoice sheet where the formula is located as well) and the list of waybills is in sheet Data Input in column B. Column D on Data Input has the list of different companies each with different waybills.
This formula has worked fine so far:

=LOOKUP(2;1/((COUNTIF($I$1:I2;'Data Input'!$B$4:$B$20000)=0)*($C$13='Data Input'!$D$4:$D$20000));'Data Input'!$B$4:$B$20000)

I need it to also take into account the month that is selected (Block B10 on Invoices sheet) so that the above formula does not give me all the waybills for every month for a particular company. I have tried doing it this way, but it only gave me a #N/A:

=LOOKUP(2;1/((AND(COUNTIF($B$10;'Data Input'!$A$4:$A$20000);COUNTIF($I$1:I1;'Data Input'!$B$4:$B$20000))=0)*($C$13='Data Input'!$D$4:$D$20000));'Data Input'!$B$4:$B$20000)

How can I modify the formula to get the desired result?
Thank you

 

[Special-K99]

Re: Help with the LOOKUP formula

You haven't said what format B10 is. Month number, month name, shortened month name, text ???
Or what format column A is in.
I'll code it just like you put it, you'll have to change it accordingly if you need to convert the month.

Just expand the condition

=LOOKUP(2;1/((COUNTIF($I$1:I2;'Data Input'!$B$4:$B$20000)=0)*($C$13='Data Input'!$D$4:$D$20000)*('Data Input'!$A$4:$A$20000=$B$10));'Data Input'!$B$4:$B$20000)

 

[MissingInAction]

Re: Help with the LOOKUP formula

Hi Special-K99
The format for B10 and column A is just plain text. I was unaware that you can just expand the formula like that, since i'm not very familiar with the LOOKUP formula.
I have copied the formula you provided, but it still gave the $N/A error. What other information do you require?

 

[Special-K99]

Re: Help with the LOOKUP formula

I think I copied the wrong formula
Does this work?

=LOOKUP(2,1/((COUNTIF($I$1:I2,'Data Input'!$B$4:$B$20000)=0)*($C$13='Data Input'!$D$4:$D$20000)*('Data Input'!$A$4:$A$20000=$B$10)),'Data Input'!$B$4:$B$20000)

 

[MissingInAction]

Re: Help with the LOOKUP formula

That formula is the same as the previous one in your first post.

 

[Special-K99]

Re: Help with the LOOKUP formula

Oh yep. So it is.

Ok,

B10 is a month just text and you say the months on the 'Data Input' sheet are also just text...

1. In B10 put the month that is in 'Data Input'!A4

2. in a blank cell on the B10 sheet put
=B10='Data Input'!A4

What is the result?

 

[MissingInAction]

Re: Help with the LOOKUP formula

The result is FALSE.
So I realized where you were going with this (or rather I should say I realized where the problem is) and made some changes to my structure. On the data input sheet I have the dates 21 Sep 19 - 20 Oct 19, but on my calculation sheet it is just called Sep 19 - Oct 19. Both are now 21 Sep 19 - 20 Oct 19.
Now your original formula is also working perfectly. Thank you very much.