INDEX-MATCH formula returns the same value in Google Sheets

0
votes

What I'm trying to achieve?

Use INDEX-MATCH formula to retrieve the PostCode by matching the Suburb in col F (Lugs tab) with the Suburb in col H (Config tab) and return the corresponding Post Code in Col I

What is the issue?

The INDEX-MATCH formula in cel BA (Lugs tab) returns the same Post code even though the suburbs are different.

={"Post Code Pickup";ARRAYFORMULA(IF(ISBLANK(F2:F)," ", INDEX( Config!A2:I, MATCH(F2:F, Config!H2:H,0),9)))}

I have tested that the MATCH formula works correctly as it returns the correct row number in col BB (Lugs tab) eg; 240, 226, 147 etc.

={"Post Code Row #";ARRAYFORMULA(IF(ISBLANK(F2:F),"", MATCH(F2:F, Config!H2:H,0)))}

Lugs tab data:

enter image description here

Config tab unsorted data is as follows:

enter image description here

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Can you share a copy of your spreadsheet? That would make it a lot easier to help you out. – JPV
the two screenshots I pasted is it. I would have to desanitized the spreadsheet as it contains sensitive data. Are the formulas I pasted no enough? – Roggie
Why do you have 9 after the match function ? – soMario
@MariosKaramanis 9 is the offset column to return eg; col 'I' Postcode – Roggie

1 Answers

2
votes

See if this works

={"Post Code Pickup"; ARRAYFORMULA(IF(ISBLANK(F2:F),, VLOOKUP(F2:F, Config!H2:I, 2, 0 )))}