Excel Formula - Partial Match and show value

1
votes

I have been trying with COUNTIF, LOOKUP and MATCH and cannot seem to work out this formula and would really appreciate the help !

There is a list of POSTCODES (Column A) in Sheet 1 with a list of PARTIAL POSTCODES (Column A) in Sheet 2, with accompanying codes - CODE 1 (Column B) and CODE 2 (Column C). What i am looking for is a formula that will :

  1. Take the POSTCODE from Sheet 1
  2. Lookup and do a partial match in POSTCODE in Sheet 2
  3. If match then take CODE 1 and CODE 2 from Sheet 2 and enter it in Sheet 1

Example of what i am looking for : http://www.theukwebspace.co.uk/example.png

2
excelmatchformulacountif
What do you mean by "partial match"? some examples would be helpful - Charles Williams
I have created an example and linked it to theukwebspace.co.uk/example.png - Grant

2 Answers

1
votes

Are you looking for excel formulas to do this for you?

How about:

=VLOOKUP((LEFT(A1, 5)), Sheet2!A:C, 2, FALSE)

In column B of Sheet1. A1 references the first column in sheet one, and you would change the 2 to a 3 in column C.

0
votes

Try this formula:

=IF(ISERROR(FIND(Sheet2!$A1;Sheet1!$A1));Sheet2!B1)

Paste this in B1 then fill the rest of the cells with it.