VLOOKUP - Matching two criteria then return adjacent column

[bbg22]

Hello there,

I have a VLOOKUP formula in place that accomplishes what I need, but the file I'm using is very large and this formula ends up taking a long time to calculate. I'm sure there must be a faster way. I have attached a sample file to demonstrate what I'm trying to do.

Basically, I'm trying to match the date in the first column with a unique letter in the column next to it. Then I would return the two value in the cells adjacent to that.

EF Pic 1.PNG

Right now I'm using a formula to combine the date and the unique letter as such: =$C6 & "-" & $D6. Then I use this same combination to look up the values that I need in the original table, as so:
=VLOOKUP($H6&"-"&I$5,$B$6:$F$23,5,FALSE)&" / "&VLOOKUP($H6&"-"&I$5,$B$6:$F$23,4,FALSE)

EF Pic 2.PNG

Can anyone think of a faster way to do this? There must be a faster or more efficient way, but I can't seem to figure it out.


Thanks for all of your help!
Ben

EF Test File - VLookup or Match.xlsx

[tigeravatar]

bbg22,

I don't know if it's any faster, but here's an index/match version that doesn't require the Combined helper column.
In your example file, this formula goes in cell I6 and gets copied over and down to cell K11:

Please Login or Register  to view this content.

[TMS]

I'm not sure it's better or faster but it does not require the helper column:

=INDEX($F$6:$F$23,MATCH($H6&I$5,$C$6:$C$23&$D$6:$D$23,0))&" / "&INDEX($E$6:$E$23,MATCH($H6&I$5,$C$6:$C$23&$D$6:$D$23,0))


It is an array formula and must be committed with Ctrl-Shift and Enter rather than just Enter


Regards, TMS