Using VLookup, Match function and replacing the cell value if not True.

[rahman4]

Hello experts,
I need to know as to how to use VLookup and Match function in excel, which can search for the exact match, and replace the cell if False. If True, then leave the data in the cell as it is.

Excel 1 spreadsheet	Excel 2 spreadsheet
BroadbandContracts.November2011toNovember2012	Broadband Contracts.November2011 to November 2012
Vendor.Contracts	Vendor.Contract
Time.Overrides.USCurrent(Dates)	Time.Overrides.USCurrent(Dates)
Time.Schedule.	Time..USJanuary2002toJuly2008
Time.Schedule.USNovember2007toCurrent	Time.Schedule.USNovember2007toCurrent
Time.Schedule.USJanuary2002toJuly2008	Vendor.PurchaseOrders.Cu
Time..USNovember2007toCurrent	Time..US November 2007 to Current
Time..USJanuary2002toJuly2008	Time.Schedule.
UnemployeementClaims	UnemployeementClaims
Vendor.PurchaseOrders.Cu	Time.Schedule.USJanuary2002toJuly2008

<colgroup><col><col></colgroup><tbody>
</tbody>

I tried this VLookup function, which leaves the data in the cell if True, and if False, it displays (#N/A).
=VLOOKUP(Sheet4!A1,Sheet5!A1:A117,1,FALSE)

Can anyone guide me as to how to use the VLookup, Match function, and replace the cell data if it does not match.

Appreciate any feedback.
Thanks,

 

[Jonmo1]

You could try to use the "closest match" type with the LOOKUP function.
This requires the data in Sheet5 to be sorted in ASCENDING order.

=LOOKUP(Sheet4!A2,Sheet5!A$2:A$117)

Notice I changed the lookup range to start in row 2.
This is assuming you have a header in A1


The "Closest Match" lookup is really meant for working with numbers. That's where it works the best.
You can get some strange and unexpected results when dealing with text, so it might not be 100% dependable.
Again, sheet5 MUST be sorted in ascending order by column A (not including the header row if there is one)

 

[rahman4]

Hmm.. That's strange to hear that we don't the option to match and replace. Well, then i think i should stop here, and use the same formula that we have already incorporated.
Thanks Jonmo1. Appreciate all your help on this.

 

[Jonmo1]

Hmm.. That's strange to hear that we don't the option to match and replace.

What is it going to replace it 'With' ?
If Match/Vlookup returns #N/A, that means it can't find the value. So what is it supposed to replace it with?

Maybe I've completely misunderstood you...