How to find a certain cell that matches a certain Item# in a different cell

I am trying to pull data from a cell that is far from the cell that it is connected to. I have tried VLOOKUP, HLOOKUP, both together, and Index and Match. I must be doing something wrong unless it is not possible, I have the cells I am trying to pull from highlighted in Yellow in column H in tab "PM". The tab that's named "Sheet1" is the one where i'm trying to pull the data to in the last column in the table(Also highlighted in yellow".

Try

in C5

=IFERROR(INDEX(PM!$H$5:$H$1942,MATCH($A5,PM!$A$5:$A$1942,0)-1),"")

That works for that column, but my Vlookup works for that column. I need help with the column to the right of it, highlighted in yellow. I do appreciate the help though.

So where is "Order Quantity" found ???

The formula I provided pulls the data from Column H of PM which is what you wanted: so put this in D5 (?)

Well i put it in D5 and it works with what I need, which is fantastic, I am curious though. Why does yours work but mine does not. I tried this formula "=Index(B2:B7,Match(A7,A2:A7,0)-1)" Granted this was on a different SS with everything on 1 SS to make it easier to work, but I feel like they are basically the same, is there a flaw in that formula that stands out?

It should work for the MATCH on A7: when you say it did not work, what was the result???

Ranges need to to absolute if formula is copied but formula is same as mine.


=Index($B$2:$B$7,Match($A7,$A$2:$A$7,0)-1)

See attached

I just kept on getting either 0 or some massive # like 37586 that was no where on the SS.