Google Sheets - Index, Match, & Importrange - What is wrong with my formula?

These are two example sheets of what I am trying to do:

Reference: https://docs.google.com/spreadsheets/d/1KT7awPkrsPPvPO3WnWt_xCPdSxd0wse54BJCfo05pog/edit?gid=0#gid=0

Summary: https://docs.google.com/spreadsheets/d/1P7k1uU2V7qAAE6SkupotfUZWQANPzS6xCAPIm0Woh-c/edit?gid=177776598#gid=177776598

I am trying to pull information from the Reference sheet into the Summary sheet. I want to pull based on the zip code I enter into the Summary sheet, cell B2, from the row matching that zip code in the Reference sheet.

I am pulling different information into certain cells, such as, City, State, Awarded Carrier, Bid, ect. using the Zip Code as the reference, please help me with my formula!

Thank you for any help!

I've thought about trying Query but I don't believe that will work, I've also tried adding a reference for the Row part of Index. I've looked up videos and such to no avail, most people are just pulling straight information rather than what I'm doing so I haven't been able to figure out how to fix it!

Edit: I originally had entire URL but you do not need entire URL for importrange

I added what I am looking for it to give me in an example tab on Summary sheet

Use vlookup(), like this:

=let( 
  data, importrange("1KT7awPkrsPPvPO3WnWt_xCPdSxd0wse54BJCfo05pog", "RFP Carrier Rates!A1:G"), 
  zip, B2, 
  zipCodes, choosecols(data, 4), 
  programs, choosecols(data, 1), 
  ifna( 
    vlookup(zip, { zipCodes, programs }, 2, false), 
    "(no such ZIP code)" 
  ) 
)

Note that the zip code 11741 does not appear in the data.

See let(), choosecols() and vlookup().