Index/Match Help

[No_Volume_4708]

My formula is showing N/A. Assuming the Match function isnt finding its match. I need help to fix this. Ive looked over everything and i'm lost.

Here is my formula:
=INDEX('Allocation Builder'!$A$2:$U$25,MATCH(Sheet1!B10,'Allocation Builder'!$A$2:$U$25,0)+1,1)
The formula is referring to this map. I need the Index/Match to pull the Cell directly below the match
Sheet1!B10 = 20101

All cells are formatted as TEXT, Generic, and Number. Nothing has worked for me.

Any help would be greatly appreciated. Thank you

 

[GlennUK]

Is there only one instance of the contents of Sheet1!B10, in range 'Allocation Builder'!$A$2:$U$25?

 

[No_Volume_4708]

Correct. Cells are very similar but do not repeat. EX: 20101, 20102, 20103... and so on.

 

[GlennUK]

You could do it like this:
=INDEX('Allocation Builder'!$A$1:$U$25,SUMPRODUCT(--('Allocation Builder'!$A$2:$U$25=Sheet1!B10)*ROW('Allocation Builder'!$A$2:$U$25))+1,SUMPRODUCT(--('Allocation Builder'!$A$2:$U$25=Sheet1!B10)*COLUMN('Allocation Builder'!$A$2:$U$25)))