Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#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 |
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using Match function with duplicate values in an array | Excel Worksheet Functions | |||
How do I access data stored in a SQL server for vlookup function? | Excel Worksheet Functions | |||
How do I sum a range which includes the "#N/A" VLOOKUP return valu | Excel Worksheet Functions | |||
VLOOKUP Function | Excel Worksheet Functions | |||
Return value with using Excel function | New Users to Excel |