vlookup function return all values
 

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

You either need to use...
DataFilterAdvanced Filter
and check 'copy to another location' and 'Unique Records only'
OR
a macro.
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"j2thea" wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$4,$D$1),INDEX(B$ 1:B$4,SMALL(IF($A$1:$A$
4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$1:E1))),"")

....where D1 contains your criterion, such as 1110.

Hope this helps!

In article ,
j2thea wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

That does help, however it is outputting the same number in 2 rows, instead
of giving me the first match and the second match.

"Domenic" wrote:

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$4,$D$1),INDEX(B$ 1:B$4,SMALL(IF($A$1:$A$
4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$1:E1))),"")

....where D1 contains your criterion, such as 1110.

Hope this helps!

In article ,
j2thea wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after typing the formula, instead of
hitting ENTER, hold both the CONTROL and SHIFT keys down, then while
those two keys are held down, hit ENTER. Excel will place braces {}
around the formula indicating that you've entered the formula correctly.

Does this help?

In article ,
j2thea wrote:

That does help, however it is outputting the same number in 2 rows, instead
of giving me the first match and the second match.

"Domenic" wrote:

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$4,$D$1),INDEX(B$ 1:B$4,SMALL(IF($A$1:$A$
4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$1:E1))),"")

....where D1 contains your criterion, such as 1110.

Hope this helps!

In article ,
j2thea wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

I did hit CONTROL+SHIFT+ENTER. Could you please rewrite the script using:
1st Column = A1:A27
2nd Column = B1:B27

that way I can make sure the formula is correct. Even with
CONTROL+SHIFT+ENTER it outputted the same number twice.

"Domenic" wrote:

Make sure that you confirm the formula with CONTROL+SHIFT+ENTER, not
just ENTER. In other words, after typing the formula, instead of
hitting ENTER, hold both the CONTROL and SHIFT keys down, then while
those two keys are held down, hit ENTER. Excel will place braces {}
around the formula indicating that you've entered the formula correctly.

Does this help?

In article ,
j2thea wrote:

That does help, however it is outputting the same number in 2 rows, instead
of giving me the first match and the second match.

"Domenic" wrote:

Try the following array formula that needs to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$4,$D$1),INDEX(B$ 1:B$4,SMALL(IF($A$1:$A$
4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS(E$1:E1))),"")

....where D1 contains your criterion, such as 1110.

Hope this helps!

In article ,
j2thea wrote:

My table for vlookup: A1:B4
1110 2 or it can have one key 1110 2
1110 3 3
1120 4 1120 4
1120 5 5

I need the function to return all data associated with the value.
so when I look up 1110, i get 2 and 3 in different cells such as
2
3

The formula would be adjusted as follows...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$27,$D$1),INDEX(B $1:B$27,SMALL(IF($A$1:$
A$27=$D$1,ROW($B$1:$B$27)-ROW($B$1)+1),ROWS(E$1:E1))),"")

Does this help?

In article ,
j2thea wrote:

I did hit CONTROL+SHIFT+ENTER. Could you please rewrite the script using:
1st Column = A1:A27
2nd Column = B1:B27

that way I can make sure the formula is correct. Even with
CONTROL+SHIFT+ENTER it outputted the same number twice.

That was extremely helpful. One more question. Would I just type Sheet2!
before each cell reference if the data is on sheet two and the formula is on
sheet one?

"Domenic" wrote:

The formula would be adjusted as follows...

E1, copied down:

=IF(ROWS(E$1:E1)<=COUNTIF($A$1:$A$27,$D$1),INDEX(B $1:B$27,SMALL(IF($A$1:$
A$27=$D$1,ROW($B$1:$B$27)-ROW($B$1)+1),ROWS(E$1:E1))),"")

Does this help?

In article ,
j2thea wrote:

I did hit CONTROL+SHIFT+ENTER. Could you please rewrite the script using:
1st Column = A1:A27
2nd Column = B1:B27

that way I can make sure the formula is correct. Even with
CONTROL+SHIFT+ENTER it outputted the same number twice.

In that case, the formula would change as follows...

=IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,S
MALL(IF(Sheet2!$A$1:$A$27=$D$1,ROW(Sheet2!$B$1:$B$ 27)-ROW(Sheet2!$B$1)+1)
,ROWS(E$1:E1))),"")

In article ,
j2thea wrote:

That was extremely helpful. One more question. Would I just type Sheet2!
before each cell reference if the data is on sheet two and the formula is on
sheet one?

vlookup function return all values
 

This formula works perfect, but because I have so much data I'd like to have
the keys in more columns than more rows. How can I search both colums and
return the information in the column beside it?

ID Value ID Value
1 1.2 2 1.3
1 1.6 2 1.7
3 1.4 4 1.5

So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i
need is with returning the value in the 4th column.

"Domenic" wrote:

In that case, the formula would change as follows...

=IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,S
MALL(IF(Sheet2!$A$1:$A$27=$D$1,ROW(Sheet2!$B$1:$B$ 27)-ROW(Sheet2!$B$1)+1)
,ROWS(E$1:E1))),"")

In article ,
j2thea wrote:

That was extremely helpful. One more question. Would I just type Sheet2!
before each cell reference if the data is on sheet two and the formula is on
sheet one?

vlookup function return all values
 

A few questions...

Can the same ID appear on the same row for both columns? If so, do both
corresponding values need to be returned? Or will there never be an
instance where the same ID appears on the same row for both columns?

In article ,
j2thea wrote:

This formula works perfect, but because I have so much data I'd like to have
the keys in more columns than more rows. How can I search both colums and
return the information in the column beside it?

ID Value ID Value
1 1.2 2 1.3
1 1.6 2 1.7
3 1.4 4 1.5

So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i
need is with returning the value in the 4th column.

vlookup function return all values
 

the id will never be on the same row. The ID, if repeated, will follow the
matching id.

"Domenic" wrote:

A few questions...

Can the same ID appear on the same row for both columns? If so, do both
corresponding values need to be returned? Or will there never be an
instance where the same ID appears on the same row for both columns?

In article ,
j2thea wrote:

This formula works perfect, but because I have so much data I'd like to have
the keys in more columns than more rows. How can I search both colums and
return the information in the column beside it?

ID Value ID Value
1 1.2 2 1.3
1 1.6 2 1.7
3 1.4 4 1.5

So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i
need is with returning the value in the 4th column.

vlookup function return all values
 

Assumptions:

A1:D1 contains ID, Value, ID, and Value

A2:D4 contains your data

F2 contains the ID of interest, such as 2

Formulas:

G2:

=SUMPRODUCT(--((A2:A4=F2)+(C2:C4=F2)0))

H2, copied down:

=IF(ROWS($H$2:H2)<=$G$2,INDEX(IF($A$2:$A$4=$F$2,$B $2:$B$4,$D$2:$D$4),SMAL
L(IF(($A$2:$A$4=$F$2)+($C$2:$C$4=$F$2),ROW($A$2:$A $4)-ROW($A$2)+1),ROWS($
H$2:H2))),"")

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
j2thea wrote:

the id will never be on the same row. The ID, if repeated, will follow the
matching id.

"Domenic" wrote:

A few questions...

Can the same ID appear on the same row for both columns? If so, do both
corresponding values need to be returned? Or will there never be an
instance where the same ID appears on the same row for both columns?

In article ,
j2thea wrote:

This formula works perfect, but because I have so much data I'd like to
have
the keys in more columns than more rows. How can I search both colums
and
return the information in the column beside it?

ID Value ID Value
1 1.2 2 1.3
1 1.6 2 1.7
3 1.4 4 1.5

So if I'm searching for ID 2 it will return 1.3 and 1.7. The main help i
need is with returning the value in the 4th column.

vlookup function return all values
 

The formula doesnt work when I use Sheet2 references. I've tried usi ng it
on a different sheet, and the same sheet, but it doesnt compute correct
values.

"Domenic" wrote:

In that case, the formula would change as follows...

=IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,S
MALL(IF(Sheet2!$A$1:$A$27=$D$1,ROW(Sheet2!$B$1:$B$ 27)-ROW(Sheet2!$B$1)+1)
,ROWS(E$1:E1))),"")

In article ,
j2thea wrote:

That was extremely helpful. One more question. Would I just type Sheet2!
before each cell reference if the data is on sheet two and the formula is on
sheet one?

vlookup function return all values
 

I think my problem is my key is a concatenated string. When I'm comparing it
in the IF string its throwing out an error. my D1 value.

"j2thea" wrote:

The formula doesnt work when I use Sheet2 references. I've tried usi ng it
on a different sheet, and the same sheet, but it doesnt compute correct
values.

"Domenic" wrote:

In that case, the formula would change as follows...

=IF(ROWS(E$1:E1)<=COUNTIF(Sheet2!$A$1:$A$27,$D$1), INDEX(Sheet2!B$1:B$27,S
MALL(IF(Sheet2!$A$1:$A$27=$D$1,ROW(Sheet2!$B$1:$B$ 27)-ROW(Sheet2!$B$1)+1)
,ROWS(E$1:E1))),"")

In article ,
j2thea wrote:

That was extremely helpful. One more question. Would I just type Sheet2!
before each cell reference if the data is on sheet two and the formula is on
sheet one?

vlookup function return all values
 

In article ,
j2thea wrote:

The formula doesnt work when I use Sheet2 references. I've tried usi ng it
on a different sheet, and the same sheet, but it doesnt compute correct
values.

Can you post the exact formula you're using?

vlookup function return all values
 

the formula works, its the fact that I'm using a concatenated string as my
key value. It passes in the CountIF portion, but it fails in the SMALL(IF
formula. I'm using the formula you provided with the sheet reference in a
previous reply. My key value is D1.

How do I get the String to be a number reference I can use?


"Domenic" wrote:

In article ,
j2thea wrote:

The formula doesnt work when I use Sheet2 references. I've tried usi ng it
on a different sheet, and the same sheet, but it doesnt compute correct
values.

Can you post the exact formula you're using?

vlookup function return all values
 

Try replacing...

D1

with

D1+0

Does this help?

In article ,
j2thea wrote:

the formula works, its the fact that I'm using a concatenated string as my
key value. It passes in the CountIF portion, but it fails in the SMALL(IF
formula. I'm using the formula you provided with the sheet reference in a
previous reply. My key value is D1.

How do I get the String to be a number reference I can use?

vlookup function return all values
 

yes, thanks


"Domenic" wrote:

Try replacing...

D1

with

D1+0

Does this help?

In article ,
j2thea wrote:

the formula works, its the fact that I'm using a concatenated string as my
key value. It passes in the CountIF portion, but it fails in the SMALL(IF
formula. I'm using the formula you provided with the sheet reference in a
previous reply. My key value is D1.

How do I get the String to be a number reference I can use?

vlookup function return all values
 

After all that, when I hide the sheets with the data numbers on it, my
functions on my first sheet dont return anything. Is there a way to hide the
sheets and still use the information?

"Domenic" wrote:

Try replacing...

D1

with

D1+0

Does this help?

In article ,
j2thea wrote:

the formula works, its the fact that I'm using a concatenated string as my
key value. It passes in the CountIF portion, but it fails in the SMALL(IF
formula. I'm using the formula you provided with the sheet reference in a
previous reply. My key value is D1.

How do I get the String to be a number reference I can use?

vlookup function return all values
 

In article ,
j2thea wrote:

After all that, when I hide the sheets with the data numbers on it, my
functions on my first sheet dont return anything. Is there a way to hide the
sheets and still use the information?

As far as I know, hiding sheets shouldn't affect your formulas. If
you'd like, I can send you a sample file.