vlookup glitch

[Marlene]

Using po numbers in column A produces the serial numbers in column B. The
issue is the PO numbers are the same but the serial numbers are unique. The
vlookup function is only pulling the first po lookup line for the serial
number. The results for serial numbers are all the same and not unique
numbers.
Column A1 to A10 same po 456890 - Column B B1-B10 serial numbers are all
unique. Results for Column B is always B1. The serial numbers b2 to b10 are
never pulled.

 

[Peo Sjoblom]

That is how VLOOKUP works, it's not really a glitch. Things like these are
better done using a filter, filtering on a PO number will give you all
instances of that number and the adjacent serial numbers. You can also get
it by using a fairly complicated array formula and copying that formula
down, example here

http://nwexcelsolutions.com/advanced_function_page.htm


number 6

but as I stated earlier it is better to use a filter

 

[Teethless mama]

Try this:

"PO" & "SN" are defined name ranges
C2: holds criteria

In D2:
=IF(ISERR(SMALL(IF(PO=$C$2,ROW(INDIRECT("1:"&ROWS(PO)))),ROWS($1:1))),"",INDEX(SN,SMALL(IF(PO=$C$2,ROW(INDIRECT("1:"&ROWS(PO)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down

 

[Marlene]

Thanks! I will try some of the formulas.

That is how VLOOKUP works, it's not really a glitch. Things like these are
better done using a filter, filtering on a PO number will give you all
instances of that number and the adjacent serial numbers. You can also get
it by using a fairly complicated array formula and copying that formula
down, example here

http://nwexcelsolutions.com/advanced_function_page.htm


number 6

but as I stated earlier it is better to use a filter