I need help with a Google Sheets formula to match data between different columns.
Here’s what I’m trying to do:
Column C contains a list of items
Column D should show values based on matches from another section
Column G has reference items
Column H has the corresponding values I want to retrieve
The logic I need:
When an item in column C doesn’t match anything in column G (ignoring case), then column D should stay empty
When an item in column C matches something in column G (case insensitive), then column D should display the value from column H in the same row as the match
For example, if C3 matches with G2847, then D3 should show the value from H2847.
What formula can I use to accomplish this lookup with case insensitive matching across a large range?
try using VLOOKUP with an exact match but make both sides UPPER. something like =IF(ISERROR(VLOOKUP(UPPER(C3),UPPER(G:H),2,FALSE)),,VLOOKUP(UPPER(C3),UPPER(G:H),2,FALSE)) - just adjust the range for your data size. it worked for me when matching customer info with mixed capitalization.
Use INDEX and MATCH with UPPER for case-insensitive matching: =IFERROR(INDEX(H:H,MATCH(UPPER(C3),UPPER(G:G),0)),""). This converts both your lookup value in C3 and the reference column G to uppercase before comparing. IFERROR keeps cells blank when there’s no match instead of throwing errors. I’ve used this tons of times with product codes and customer names where case was inconsistent. INDEX-MATCH is way more flexible than VLOOKUP too since your columns don’t need to be next to each other. Just copy the formula down column D and you’re set.
Try XLOOKUP if you’ve got it - =XLOOKUP(UPPER(C3),UPPER(G:G),H:H,""). It handles errors automatically and returns blank when there’s no match. I’ve been using this since XLOOKUP came out and it’s way cleaner than wrapping INDEX-MATCH in IFERROR. The UPPER case conversion works the same but the syntax is simpler. Plus it’s faster than VLOOKUP on big datasets since your lookup column doesn’t have to be on the left.