Multi condition lookup

Hi All,

I am trying to achieve this "If A2=P2 THEN LOOK FOR VALUE IN COLUMN Q AND PULL VALUE FROM CELL THAT CORRISPONDS WITH JAN AND PART #" I tried vlookup and match index and have failed. Any help will be greatly appreciated.


Book1.xlsx

Hope this does what you want.

Part Lookup 2.xlsx

I took your formula and plugged it into the sample I posted, It didn't work it returned blanks.

It is coded to return blanks where there is no match. For example there is no part# 1000 nor 1659 in the sample. Also the dates listed in the sample are January through May, so it returns blanks for June through December. I tested it with your sample and it returns the quantity for part#'s and months listed in O1:Q24

Correct, however it is returning blanks for value that are there and should show up. Am I using your formula the right way?
Book2.xlsx

The formula is an array formula and has to be entered with `Ctrl`+`Shift`+`Enter` pressed at the same time in order to work. I have already entered it in this updated version of the workbook that you sent.

Update of Book2.xlsx

Hope this helps.

yes of course! I have some tweaks to make and I will let you know if it works.
Thank you

After making tweaks, if the solution provided is satisfactory please mark the thread solved. If any posters in this thread have been helpful please add reputation.

Hope that you have a good day.

Can another field be added? This is the first time I work with array formulas.
Book4.xlsx

For a first time you did very well and got close. Your formula was referencing C1 which actually does not have a value as "JAN" is in B1 and then merged and centered. This is not really a problem in this case as you can simply reference your formula to B1 instead (see attached file). Remember to press `Ctrl`+`Shift`+`Enter` concurrently and then copy down. The only problem, if we want to call it that, comes in when applying the formula to the cells in columns E,G,I etc. You can not just copy down and across because that would erase the formulas in columns D,F,H etc. We could use a fancy if formula with the R1C1 format and say that if the column was even paste one formula and if odd paste another, but with so few columns to have to populate I think that it would be easier to just copy C2 and paste to E2,G2,I2 etc. then copy down in each of those columns. I have already populated C2:C7 and E2 in the attachment.

Update of Book4.xlsx

Hope this helps. If this solution is satisfactory please mark the thread closed. If anyone who posted in this thread was helpful please add to their reputation. If you have questions which go further than the material already covered, please open a new thread, and we will be glad to help.

Thank you!