VLOOKUP - Questions

[Terrick]

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.

Size	Style	Price	Date
24 x 42	Harvey 2	42.88	4-12-19
30 x 42	Harvey 2	45.05	4-12-19
42 x 42	Harvey 2	52.58	4-12-19
24 x 42	Harvey 2	43.08	6-27-19

<tbody>
</tbody>

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?

Size	Style	Price	Date
24 x 42	Harvey 2	42.88	4-12-19

<tbody>
</tbody>

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

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

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

 

[Roncondor]

Hi

1. Never use VLOOKUP - Always use INDEX and MATCH instead

2. You should use a range names for your table since if you change the sheet name and/or move the table, the macro will not work anymore

3. What you can do is a loop through the table and then check each entry to see if it matches and then store

 

[kweaver]

Without a range name at present, in A9 put one of the styles (e.g., Harvey 2).
Then, change B9 in my original to:

=IFERROR(INDEX(B$1:B$5,SMALL(IF(($C$1:$C$5=$A$9)*($B$1:$B$5=$A$8),ROW($B$1:$B$5)),ROW(1:1))),"")

NOTE: still an array formula.

Then, fill across and down.

	Size	Style	Price	Date
	24 x 42	Harvey 2	42.88	4/12/2019
	24 x 42	Harvey 3	45.05	4/12/2019
	42 x 42	Harvey 2	52.58	4/12/2019
	24 x 42	Harvey 2	43.08	6/27/2019
24 x 42	Size	Style	Price	Date
Harvey 2	24 x 42	Harvey 2	42.88	4/12/2019
	24 x 42	Harvey 2	43.08	6/27/2019

<colgroup><col span="4"><col></colgroup><tbody>
</tbody>