CODEWITHSUNDEEP
google-sheets
HSHO
HSHO

Reputation: 525

How to Find exact Match then Paste Similar Value to adjacent Cell

I am trying to create a function which match the exact value in the raw string if macthes then paste the value but here Lookup function is giving an wrong answer.

I know how to extract it using Left function but how to macth exact string like in VBA we use Criteria: "" & CellValue & "".

=LOOKUP(99^99,SEARCH($K$3:$K$97,A3),$K$3:$K$97)

This function is not matching exact string as you can see in attached picture.

enter image description here

Sheet Link

=IF(RegExMatch(B2,'Match Criteria'!B:B),"YES","NO")

Upvotes: 0

Views: 283

Answers (2)

Nikko J.
Nikko J.

Reputation: 5533

EDIT

Paste this in cell C3 of your Sample Spreadsheet:

=ARRAYFORMULA(IFERROR(VLOOKUP(IFNA(REGEXEXTRACT(B3:B, "\b([A-Za-z]{2})\b(?:\s[\d{5}.]|$)"), REGEXEXTRACT(B3:B, "\b([A-Za-z]{4})\b(?:\s\d{5})")), 'Match Criteria'!B2:B, 1, FALSE)))

and for D3:

=ARRAYFORMULA(IFERROR(VLOOKUP(REGEXEXTRACT(TO_TEXT(B3:B), "([\d]{5}\-[\d]{4}|[\d]{5})"),'Match Criteria'!A2:A,1,FALSE),))

Note: Make sure to change the format of Columns A & B of Sheet Match Criteria to Plain Text.

Example output:

enter image description here

REFERENCES:

Upvotes: 1

HSHO
HSHO

Reputation: 525

I have developed this solution but it is extremly slow.

=OFFSET('Match Criteria'!$B$1,(SUMPRODUCT(--NOT(LEN(B3)=LEN(SUBSTITUTE(B3," "&'Match Criteria'!$B$2:$B&" ","")))*(ROW('Match Criteria'!$B$2:$B)-1))),)

Upvotes: 0

Related Questions