Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
j2thea
 
Posts: n/a
Default 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
  #3   Report Post  
Domenic
 
Posts: n/a
Default

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

  #4   Report Post  
j2thea
 
Posts: n/a
Default

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


  #5   Report Post  
Domenic
 
Posts: n/a
Default

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




  #6   Report Post  
j2thea
 
Posts: n/a
Default

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


  #7   Report Post  
Domenic
 
Posts: n/a
Default

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.

  #8   Report Post  
j2thea
 
Posts: n/a
Default

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.


  #9   Report Post  
Domenic
 
Posts: n/a
Default

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?

  #10   Report Post  
j2thea
 
Posts: n/a
Default 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?




  #11   Report Post  
Domenic
 
Posts: n/a
Default 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.

  #12   Report Post  
j2thea
 
Posts: n/a
Default 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.


  #13   Report Post  
Domenic
 
Posts: n/a
Default 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.


  #14   Report Post  
j2thea
 
Posts: n/a
Default 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?


  #15   Report Post  
j2thea
 
Posts: n/a
Default 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?




  #16   Report Post  
Domenic
 
Posts: n/a
Default 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?
  #17   Report Post  
j2thea
 
Posts: n/a
Default 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?

  #18   Report Post  
Domenic
 
Posts: n/a
Default 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?

  #19   Report Post  
j2thea
 
Posts: n/a
Default 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?


  #20   Report Post  
j2thea
 
Posts: n/a
Default 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?




  #21   Report Post  
Domenic
 
Posts: n/a
Default 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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Match function with duplicate values in an array Richard Excel Worksheet Functions 3 April 22nd 23 07:45 PM
How do I access data stored in a SQL server for vlookup function? M.Heer Excel Worksheet Functions 8 May 12th 05 09:51 PM
How do I sum a range which includes the "#N/A" VLOOKUP return valu Sailor Excel Worksheet Functions 6 May 9th 05 08:46 AM
VLOOKUP Function Neoseraph Excel Worksheet Functions 12 April 4th 05 12:21 AM
Return value with using Excel function SNOWBALLCHAN New Users to Excel 1 February 21st 05 08:25 AM


All times are GMT +1. The time now is 10:01 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"