MissingInAction

Board Regular
Joined
Sep 20, 2019
Messages
85
Office Version
  1. 365
Platform
  1. Windows
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:
Code:
=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:
Code:
=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
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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)
 
Upvote 0
Solution
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?
 
Upvote 0
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)
 
Upvote 0
Re: Help with the LOOKUP formula

That formula is the same as the previous one in your first post.
 
Upvote 0
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?
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,267
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top