Lookup value using two separate dynamic ranges.

[SpirePeter]

I have created two custom formulas that each return a two dimensional array that result in dynamic arrays that spill to adjacent fields.
The following examples are a mockup of my actual formulas as they are large.

Dynamic array 1 is on it's own sheet called "Orders" and looks like this:
Custom formula is in cell A2 and spills to A2:B4

On another sheet, "Inventory", I have Dynamic array 2.
Custom formula is in A2 and spills to A2:B3

I want a way, likely using something like vlookup, xlookup, index etc to use the PartNo in the Orders sheet to determine the Onhand from the Inventory sheet.

The result would be this:

Ideally, the results in C would also expand down dynamically, when the list of orders returned grows.
But I could manage if I had to manually extend the formula when "Orders" has been refreshed.

 

[Fluff]

How about

=VLOOKUP(INDEX(A2#,,2),Inventory!A2#,2,0)

 

[SpirePeter]

That's fantastic, exactly what I needed.
It even fills down when the "Orders" sheet refreshes.

Thank-you so much.

 

[Fluff]

You're welcome & thanks for the feedback.