Rows to reference Columns

Heval A.

Hi,

I have a Form that adds rows to the Consignment Stock Take sheet (seen below). Inventory items in this sheet are arranged by columns. Furthermore, I have an Inventory Management sheet (seen below) which has Inventory Items arranged in rows.

Problem/Desired Outcome

I want the [In Stock] Inventory Management Column to return the cell value of it's respective Inventory Item (from Consignment Stock Sheet). I also want the Stock Check Date of this returned cell value to equal to the Stock Take Date.

I was able to solve the problem by using the below function (referencing individual columns). However, I reached the cross-sheet referencing limit:

=INDEX({Consignment #2 Column}, MATCH([Stock Check Date]@row, {Consignment Stock Take Date}))

I even tried adding-in a helper row with column names, but I still couldn't crack the formula

Any help would be highly appreciated.

Thanks!

Heval A.

I managed to solve the problem using the helper row.

However, if there are two Stock Take Dates which are the same, the formula references the top most date. From what I understand, the MATCH function searches from top to bottom.

Is there a way the MATCH function can search from bottom to top?

Here is the formula:

=INDEX({All Columns - Consignment}, MATCH([Stock Check Date]@row, {Consignment STD}), MATCH([Inventory Item]@row, {Helper Row - Consignment}, 0))

Thanks 😀

Jaykel Torres

Hey @Heval A.,

Assuming the Date Column is in chronological order and you will always want to return a value with the corresponding latest date, we can utilize the INDEX, COLLECT, and COUNTIF Functions to perform this action. I've created the example below for your reference:

Formula (highlighted in yellow): =INDEX(COLLECT(Number1:Number2, Letter1:Letter2, "B"), COUNTIF(Letter1:Letter2, "B"))

• Using the COLLECT Function, we can create a Range that only includes "B"
• Using the COUNTIF Function, we can count how many Rows that include "B"
  • In this case it is 2, which is the exact position we would like to return within our Range (it being the latest entry of "B")

I hope this helps!

Jaykel