vlookup function return all values

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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...
Data>Filter>Advanced Filter
and check 'copy to another location' and 'Unique Records only'
OR
a macro.
HTH,
 
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!
 
That does help, however it is outputting the same number in 2 rows, instead
of giving me the first match and the second match.
 
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?
 
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.
 
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?
 
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?
 
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))),"")
 
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.
 
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?
 
the id will never be on the same row. The ID, if repeated, will follow the
matching id.
 
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!
 
The formula doesnt work when I use Sheet2 references. I've tried usi ng it
 
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 said:
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.
Click to expand...

Can you post the exact formula you're using?
 
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?
 
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?
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with formula 1
nearest match 4
Scrabble Value calculation for Welsh words 0
Calculate new value from existing value? 3
IIF function for 3 criteria, possible? 2
Formula query 2
Complex lookup array with 4 variables 4
Search/Extract Specific Data from all fields in a table 1

Back
Top