I’m having trouble creating a formula that pulls data from one Google Sheet to another using a lookup value. I have two sheets - one contains reference data and the other is where I want to display the results.
In my main sheet, I enter a postal code in cell B2. I want this to search through my reference sheet and find the matching row, then return specific information like city name, state, carrier details, and bid amounts.
I’ve been trying different combinations of INDEX, MATCH, and IMPORTRANGE but can’t get it working properly. The formula should look up the postal code and bring back the corresponding data from different columns in the reference sheet.
I thought about using QUERY function but that doesn’t seem like the right approach for this. I’ve also tried adding row references to the INDEX part but still no luck. Most tutorials I found online show simple data pulling rather than this type of cross-sheet lookup.
Can someone help me figure out what’s wrong with my approach? Any working examples would be really helpful!
Had the same headache last week! First, make sure your importrange is authorized - you might be getting #REF errors without noticing. Try =INDEX(IMPORTRANGE("your-sheet-url","Sheet1!A:Z"),MATCH(B2,IMPORTRANGE("same-url","Sheet1!A:A"),0),5) where column 5 has your city data. Don’t forget quotes around your range!
This happens all the time with nested functions. Don’t try to build the whole formula at once - break it into pieces. First, make a helper column and test just the MATCH function on your imported range. Make sure it’s pulling the right row number. Once that’s working, wrap INDEX around it. Also check if your postal codes match between sheets. I’ve seen this where one sheet has text and the other has numbers, which kills the lookup. Try using TRIM and VALUE to clean up postal codes before matching - they love to have extra spaces or leading zeros that mess things up.
Fought with this for months before figuring it out. The game-changer was splitting IMPORTRANGE into separate calls - one for lookup column, one for data range. Don’t try cramming everything into one giant range. So I use: =INDEX(IMPORTRANGE("sheet-url","Sheet1!E:E"),MATCH(B2,IMPORTRANGE("sheet-url","Sheet1!A:A"),0)) where A has postal codes and E has city names. You’ll need to authorize each IMPORTRANGE separately - annoying but required. Google Sheets is super picky about data types in cross-sheet lookups. If your postal codes mix formats (some with leading zeros, some without), MATCH fails silently. Converting both sides to text with the TEXT function usually fixes weird matching problems.