Vlookup consecutive matches

Below is similar to the table I am using. I need to lookup both LOT and VAR and return a PAL and then in the next cell I need to lookup what the next match is. So the return table is kinda like a summary of the LOT + VAR listing all the PAL matches in separate cells. Any idea how to construct such a lookup?

Lookup

Lot	Var	Pal
BP18001	BLB 12X6	029
AG18001	BLB 12X6	001
BP18001	BLB 12X6	027
HS18001	BLB 12X6	002
BP18001	BLB 1P	023

RETURN

BP18001 BLB 12X6

029

027

Hello MJetter and Welcome to Excel Forum.
Using the data in post #1, an array entered formula* that will yield the expected results (see attached file) is:

Formula:

Please Login or Register  to view this content.

*Array entered formulas are confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Let us know if you have any questions.

MJetter, I received your message.
1) Select only the first cell into which you want the results displayed and paste the formula into the formula bar,
2) Be sure to enter as an array (simultaneously press Ctrl, Shift and Enter) before you copy down the column.
3) I doubt that a 30 sheet workbook could be uploaded as the limit is 1000 Kb so if 1 and 2 don't resolve the issue I would suggest making a workbook with perhaps two sheets and even then we should only need a small amount of data to work with.
4) I am including the file from post #4, I changed the last entry so that the 'VAR' is BLB 12X6 and the formula automatically picked it up.
Let us know if you have any questions.

Use this, in C4, copied across:

=IFERROR(INDEX(Resources!$D:$D,AGGREGATE(15,6,ROW(RLOT)/((RLOT=$E$1)*(RVAR=$K$2)),COLUMNS($C:C))),"")

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.