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.
[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.

=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.

[TABLE="width: 386"]
<colgroup><col span="4"><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]Size[/TD]
[TD]Style[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]24 x 42[/TD]
[TD]Harvey 3[/TD]
[TD="align: right"]45.05[/TD]
[TD="align: right"]4/12/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]42 x 42[/TD]
[TD]Harvey 2[/TD]
[TD="align: right"]52.58[/TD]
[TD="align: right"]4/12/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24 x 42[/TD]
[TD]Size[/TD]
[TD]Style[/TD]
[TD]Price[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]Harvey 2[/TD]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD="align: right"]42.88[/TD]
[TD="align: right"]4/12/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]24 x 42[/TD]
[TD]Harvey 2[/TD]
[TD="align: right"]43.08[/TD]
[TD="align: right"]6/27/2019[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]