Lookup Returning Wrong Value

[MJetter]

I've attached a sample file of what I am running into issues with. I have a table of information, one column is an identifier number and the next column is the quantity associated with that identifier. The lookup is just trying to find the identifier and then return the quantity for that identify from the next column. The formula I'm using is "=IFERROR(LOOKUP(B1,Sheet1!$D$2:$E$31,Sheet1!$E$2:$E$31), "")". But it keeps either returning no number or the wrong number.

If you open the file the lookup is on sheet 2.

[Sam Capricci]

if you have a distinct set of numbers in column D (no repeats for example) then this should work for you...
=IFERROR(INDEX(Sheet1!$E:$E,MATCH(Sheet2!B1,Sheet1!$D:$D,0)),"")

BTW, the lookup works if the values are sorted ascending, so to return 20 you would have to sort sheet1 by column D ascending.

[63falcondude]

Try this in B2:

=IFERROR(VLOOKUP(B1,Sheet1!$D:$E,2,0),"")

Drag it through D2.

[tim201110]

To use the array form, your data must be sorted.

[MJetter]

I love this forum. Thanks everyone!

[63falcondude]

You're welcome. Glad we could help.