Help needed with Index, Match, and Importrange formula in spreadsheets

Other Questions Google Sheets
1

I’m stuck on a spreadsheet problem. I’ve got two sheets: one with reference data and another for summaries. I want to pull info from the reference sheet into the summary sheet based on zip codes.

Here’s what I’m trying to do:

  1. Enter a zip code in cell B2 of the summary sheet
  2. Pull matching data (city, state, carrier, bid, etc.) from the reference sheet
  3. Display this data in specific cells of the summary sheet

I’ve tried using INDEX, MATCH, and IMPORTRANGE, but I can’t get it to work right. I thought about using QUERY, but I’m not sure if that’s the best approach. Most tutorials I’ve found don’t cover this exact scenario.

I’ve added an example tab in the summary sheet to show what I’m aiming for. Any ideas on how to fix my formula? Thanks for any help!

2

hey, have you tried using FILTER with IMPORTRANGE? it’s pretty neat for this kinda stuff. something like:

=FILTER(IMPORTRANGE(“url”, “sheet!A:Z”), IMPORTRANGE(“url”, “sheet!A:A”)=B2)

just replace “url” and “sheet” with your actual stuff. it’ll pull all matching data for the zip in B2. lemme know if you need more help!

3

I’ve encountered similar challenges with complex data retrieval in spreadsheets. For your situation, I’d recommend using a combination of VLOOKUP and IMPORTRANGE. Here’s a basic structure that should work:

=VLOOKUP(B2, IMPORTRANGE(“reference_sheet_url”, “sheet_name!A:Z”), column_number, FALSE)

Replace “reference_sheet_url” with the actual URL of your reference sheet, “sheet_name” with the name of the specific sheet, and adjust the column range and column_number as needed.

This formula looks up the zip code from B2 in the imported range and returns the corresponding data. You’ll need to set up separate formulas for each piece of information you want to pull (city, state, carrier, etc.).

Remember to grant access between the sheets if you haven’t already. If you’re still having trouble, I’d be happy to take a closer look at your specific setup.

4

I’ve been there, struggling with complex formulas in spreadsheets. From my experience, QUERY is actually a great solution for what you’re trying to do. It’s powerful and flexible, especially when combined with IMPORTRANGE.

Here’s a formula that should work for you:

=QUERY(IMPORTRANGE(“reference_sheet_url”, “sheet_name!A:Z”), “SELECT Col2, Col3, Col4, Col5 WHERE Col1 = '” & B2 & “'”)

Replace the column numbers with the actual columns containing your data. This formula will pull all the matching data for the zip code in B2 in one go.

One tip: make sure you’ve granted access between the sheets. Also, QUERY can be a bit finicky with data types, so double-check that your zip codes are formatted consistently across both sheets.

If you’re still stuck, feel free to share more details about your sheet structure. I’d be happy to help fine-tune the formula.