how to catch the second or more cell with vlookup

[Valley]

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?

 

[Don Guillett]

what do you want to do with em after they are caught?
You may be wanting to look at countif or sumif functions.

 

[Guest]

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:"&ROWS(A$1:A$8))),""),ROWS(B$14:B14))))

 

[Valley]

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?

My meaning is

If the matching value is two or more, how to catch the second and the
third and more.

For example,
Value is 16444050(in columnA1 and A2) in sheet1
There are 16444050(in column A1 and A2) and 22401241(in column B1) and
224016549(in column B2) in sheet2

When using vlookup, it can only catch B1 but not B2 of sheet2, how to
catch B1 and B2, or how to catch B2 but not B1?

 

[JE McGimpsey]

See the "Arbitrary Lookups" section here:

http://cpearson.com/excel/lookups.htm

 

[Valley]

This function only can catch small or big, but

My meaning is

Value is a column including many cells but not a cell, and several same
data corresponds maybe several different data in each cell, and I need
to catch all.
The menioned below is only a sample.

 

[Ken Johnson]

Hi Valley,
One way is to insert a new column in between columns A and B then into
the top of the new (empty) column B paste and fill down this formula...

=A1+(COUNTIF(A:A,A1)-COUNTIF(A1:A$65536,A1))*0.0001


This formula adds 0.0001*n to every succeeding recurrence of a column A
value, where n is the number of previous recurrences. If the number of
column A repetitions could exceed 10000 (as if!) you will have to
change the small number,0.0001, to a suitable smaller value so that
small number * greatest number of recurrences is less than 1.

So, your first 16444050 remains that value in the new column B and
doing a VLOOKUP based on the new column B will return 22401241.
The second 16444050 in column A is the second occurrence of that value,
so in the new column B it has been converted to 1644405.0001, so doing
a VLOOKUP with 16444405.0001 will return 224016549.
The next (3rd) occurrence of 16444050 would be converted to
16444050.0002 and this value should be used when trying to find the
VLOOKUP value in column B.

One way of setting up the VLOOKUP is, assuming C1:E2 are available...

In C1 the text "Column A Value"
In C2, type in the column A value for the VLOOKUP to process
In D1 the text "Occurrence"
In D2, type in which occurrence. This is an integer between (and
including) 1 and the total number of occurences for that column A
value. If you exceed the total number the VLOOKUP just returns its
error result.
In E1 the text "Column B Value"
In E2 the VLOOKUP formula...

=VLOOKUP(C2 + (D2-1)*0.0001,B1:C65536,2,FALSE)

you might want to change the C65536 part of the address to indicate the
number of rows occupied by your data.

Hope this makes sense!
This is the way I have overcome the problem of recurring values in a
VLOOKUP table.

Ken Johnson

 

[Valley]

Hi Johnson,
Thanks very much! This surely is a good way.

BR
Valley

 

[Ken Johnson]

Hi Valley,
You're welcome.
Thanks for the feedback.
JMB's solution and Chip Pearson's Arbitrary Lookup both work.
Perhaps you weren't using Control + Shift + Enter when entering their
formulas.
Ken Johnson