VLOOKUP - Questions

Terrick · Jun 28, 2019

Hey guys,

I'm currently trying to create a "cheat sheet" for some Pricing.
Currently, "Sheet1" looks like this. I have each column represented for the Size/Style/Price/Date.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Style[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD]42.88[/TD]
[TD]4-12-19[/TD]
[/TR]
[TR]
[TD]30 x 42[/TD]
[TD]Harvey 2[/TD]
[TD]45.05[/TD]
[TD]4-12-19[/TD]
[/TR]
[TR]
[TD]42 x 42[/TD]
[TD]Harvey 2[/TD]
[TD]52.58[/TD]
[TD]4-12-19[/TD]
[/TR]
[TR]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD]43.08[/TD]
[TD]6-27-19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
My questions is, can I get my "Sheet2" VLOOKUP to return multiple pricing and dates? I currently have it setup to only return 1 Item. But as you can see in the above table, I have ( 2 ) 24 x 42 with different pricing and different dates.
Is it possible to have VLOOKUP return each of those items without repeating and putting the most current date at the top then descend?
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Size[/TD]
[TD]Style[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD]42.88[/TD]
[TD]4-12-19[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

EDIT:
This is the code I'm using to find each value, but need to return multiple.

Code:

=VLOOKUP(A2,Sheet1!A2:D22,2,FALSE)

Hopefully I explained this well enough for someone to help.
Thanks,
Terrick

mole999 · Jun 28, 2019

vlookup ONLY can deal with one item, there other ways but I m not adept at producing them

Terrick · Jun 28, 2019

mole999 said:
vlookup ONLY can deal with one item, there other ways but I m not adept at producing them

I was thinking something like Index & Match might be able to do it, but I've never used that formula much.

kweaver · Jun 28, 2019

Here's a non-vba option where you can see you'll have to fill down for however many items are in your data:

	A	B	C	D
Size	Style	Price	Date
24 x 42	Harvey 2
30 x 42	Harvey 2
42 x 42	Harvey 2
24 x 42	Harvey 2


24 x 42	Size	Style	Price	Date
24 x 42	Harvey 2
24 x 42	Harvey 2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]45.05[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52.58[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet11

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B8[/TH]
[TD="align: left"]{=IFERROR(INDEX(B$1:B$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C8[/TH]
[TD="align: left"]{=IFERROR(INDEX(C$1:C$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D8[/TH]
[TD="align: left"]{=IFERROR(INDEX(D$1:D$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E8[/TH]
[TD="align: left"]{=IFERROR(INDEX(E$1:E$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Terrick · Jun 28, 2019

kweaver said:
Here's a non-vba option where you can see you'll have to fill down for however many items are in your data:

A B C D E
Size Style Price Date
24 x 42 Harvey 2
30 x 42 Harvey 2
42 x 42 Harvey 2
24 x 42 Harvey 2
24 x 42 Size Style Price Date
24 x 42 Harvey 2
24 x 42 Harvey 2

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"]45.05[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"]52.58[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"][/TD]

[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"][/TD]

[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet11

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]B8[/TH]
[TD="align: left"]{=IFERROR(INDEX(B$1:B$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH]C8[/TH]
[TD="align: left"]{=IFERROR(INDEX(C$1:C$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH]D8[/TH]
[TD="align: left"]{=IFERROR(INDEX(D$1:D$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
[TR]
[TH]E8[/TH]
[TD="align: left"]{=IFERROR(INDEX(E$1:E$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

kweaver,
Thanks for this reply. But I'm a bit confused. Why are we looking at things in K & J? They are empty columns. Could you explain a little further? I couldnt get this to work.

kweaver · Jun 28, 2019

Sorry about that. I was using another sheet to test. Try this:

Book1

A

B

C

D

E

1

Size

Style

Price

Date

2

24 x 42

Harvey 2

42.88

4/12/2019

3

24 x 42

Harvey 2

45.05

4/12/2019

4

42 x 42

Harvey 2

52.58

4/12/2019

5

24 x 42

Harvey 2

43.08

6/27/2019

6

7

8

24 x 42

9

24 x 42

Harvey 2

42.88

4/12/2019

10

24 x 42

Harvey 2

45.05

4/12/2019

11

24 x 42

Harvey 2

43.08

6/27/2019

12

13

Sheet11

Cell Formulas
Range	Formula
B8	{=IFERROR(INDEX(B$1:B$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
C8	{=IFERROR(INDEX(C$1:C$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
D8	{=IFERROR(INDEX(D$1:D$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
E8	{=IFERROR(INDEX(E$1:E$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

kweaver · Jun 28, 2019

It messed up again!! See if this is right:

Book1

A

B

C

D

E

1

Size

Style

Price

Date

2

24 x 42

Harvey 2

42.88

4/12/2019

3

24 x 42

Harvey 2

45.05

4/12/2019

4

42 x 42

Harvey 2

52.58

4/12/2019

5

24 x 42

Harvey 2

43.08

6/27/2019

6

7

8

24 x 42

9

24 x 42

Harvey 2

42.88

4/12/2019

10

24 x 42

Harvey 2

45.05

4/12/2019

11

24 x 42

Harvey 2

43.08

6/27/2019

12

13

Sheet11

Cell Formulas
Range	Formula
B8	{=IFERROR(INDEX(B$1:B$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
C8	{=IFERROR(INDEX(C$1:C$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
D8	{=IFERROR(INDEX(D$1:D$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
E8	{=IFERROR(INDEX(E$1:E$5,SMALL(IF($K$1:$K$5=$J$8,ROW($K$1:$K$5)),ROW(1:1))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

kweaver · Jun 28, 2019

Still messed up for some reason. Change all those K references to $B$1:$B$5.
And change the J ref to $A$8.
groan.

Terrick · Jun 28, 2019

kweaver said:
Still messed up for some reason. Change all those K references to $B$1:$B$5.
And change the J ref to $A$8.
groan.

kweaver,
This is exactly what I was looking for. Its a little pain to have to clarify how many returns I want, but I can deal with that for now.
Thanks so much for the help!

Terrick

Terrick · Jun 28, 2019

kweaver said:
Still messed up for some reason. Change all those K references to $B$1:$B$5.
And change the J ref to $A$8.
groan.

Sorry for the Double post.

kweaver,
Is it possible to do this with the addition of only showing a certain Style parameter? Say I had some that are Harvey 2 and some Harvey 3. But I only want to show the returns of the Harvey 3?

VLOOKUP - Questions

Terrick

New Member

Excel Facts

mole999

Well-known Member

Terrick

New Member

kweaver

Well-known Member

Terrick

New Member

kweaver

Well-known Member

kweaver

Well-known Member

kweaver

Well-known Member

Terrick

New Member

Terrick

New Member

Similar threads

Forum statistics

Share this page

VLOOKUP - Questions

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Well-known Member

Well-known Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock