I have a spreadsheet with multiple tabs.
One tab is a material list and another tab is a contract.
I want to create a formula that will lookup if there is a number in the quantity column on the material list tab and enter the material name on the contract tab.
So it will only enter the material information on the contract (tab) if the quantity is more than zero.
I don't want any materials on the contract that are not being used...
I hope this makes sense... lol
Thx!
Hi and welcome to the forum,
It probably makes sense... but it would be much better if you could upload a workbook with sample data and expected results.
Otherwise, it's difficult to give you a precise answer...
GC Excel
If this post helps, then click the star icon (*) in the bottom left-hand corner of my post to Add reputation.
See if you can adapt this...
Lookup with multiple instances of the lookup value
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
I attached the file I am working on.
So if there is a number in Column B on the Materials tab, I want it to show up on the Contract tab under A24, and list all the materials that have an amount.
I have tried an IF function, VLOOKUP and LOOKUP. It will only find the last material item that has a quantity, not the first one or the rest...
Thx!
I also tried
=IFERROR(INDEX(Materials!A4:Materials!A72,SMALL(IF(Materials!B$4:Materials!B$72>0,ROW(Materials!B$4:Materials!B$72)),ROWS(Contract!A$24:Contract!A24))),"")
which only returned the value of A24 in the Materials tab...
I also tried
=LOOKUP("zzzzz",CHOOSE({1,2},"",INDEX(Materials!A:Materials!A,SMALL(IF(Materials!B$4:Materials!B$72>0,ROW(Materials!B$4:Materials!B$72)),ROWS(A$26:A26)))))
which only returned a blank cell...
I don't know where you want the formula but this works as far as I can see from your formula in msg #5
Array Enter this (Ctrl + Shift + Enter)
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Try it like this...
First...
Unmerge the cells on row 24 (A24:E24)
Move the cell that says Labour.
This array formula** entered in A24:
=IFERROR(INDEX(Materials!A:A,SMALL(IF(Materials!B$4:B$72>0,ROW(Materials!B$4:B$72)),ROWS(A$24:A24))),"")
** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.
Copy down until you get blanks.
Thanks Tony! That worked!
Now, take a look at the formula that Tony wrote in Msg #8 and mine in Msg #7 and compare them with your own formula in msg #5. You were oh so close.
You're welcome. Thanks for the feedback!
If your question has been solved please mark the thread as being solved.
In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks