vlookup glitch
 

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.

vlookup glitch
 

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


--
Regards,

Peo Sjoblom



"Marlene" wrote in message
...
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.

vlookup glitch
 

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,R OW(INDIRECT("1:"&ROWS(PO)))),ROWS($1:1))))

ctrl+shift+enter, not just enter
copy down


"Marlene" wrote:

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.

vlookup glitch
 

Thanks! I will try some of the formulas.


"Peo Sjoblom" wrote:

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


--
Regards,

Peo Sjoblom



"Marlene" wrote in message
...
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.