yogi_For Values In Column A of 'Large' Lookup Values In Columns A:C of 'Small'

Google Spreadsheet   Post  #2563

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Jan-02-2019

question by: Timothy Hanson:
https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/dQPfq7dK45E;context-place=mydiscussions
how to vlookup a smaller range in larger range to the end of the larger range

Hello,

I have a range on a sheet called "Large" of 40,000 rows and on a called sheet "Small" of 550 rows.  Both in column A

I can use Vlookup to search for all the matches of the large sheet in the smaller sheet

    =ArrayFormula(iferror(vlookup('Large'!A2:A, 'Small'!A$2:E, {1,3}, false)))

and this searches to the 40,000th row

But I need to go the other way I need to search 

    =ArrayFormula(iferror(vlookup('Small'!A2:A, 'Large'!A$2:E, {1,3}, false)))

and have it search through all 40,000 rows of "Large"

But this only searches to the 550th row which is the range height of "Small"

I need to do it this way because I need the posted back columns {1,3} of Large sheet not of Small sheet

I have searched but have found nothing to help with this

Thanks

Here is an example sheet

https://docs.google.com/spreadsheets/d/1i4UpD-9osVz7K8NAzAYM61_BAnwdXIsWodQrKaYi9n8/edit?usp=sharing