how to catch the second or more cell with vlookup
One way - assuming your lookup table is in A1:B8, A14 contains your criteria,
enter in B14 (you must hold down Control+Shift keys while hitting Enter when
you key these formulae in):
=INDEX(B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT( "1:"&ROWS(A$1:A$8))),""),ROWS(B$14:B14)))
Copy down until you get an error. Or, if you don't like error messages,
=IF(ROWS(B$14:B14)COUNTIF(A$1:A$8,A$14),"",INDEX( B$1:B$8,SMALL(IF(A$1:A$8=A$14,ROW(INDIRECT("1:"&RO WS(A$1:A$8))),""),ROWS(B$14:B14))))
"Valley" wrote:
When lookup_value corresponds several value in table array, it only
catchs the first value/cell with vlookup function, how to catch the
second and more value?
|