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,

 

[Vidar]

It will not replace what's in the cell but it will give you the result in the cell you put this formula into.
=IF(ISNUMBER(MATCH(Sheet4!A1,Sheet5!$A$1:$A$10,0)),Sheet4!A1,NA())

 

[rahman4]

It will not replace what's in the cell but it will give you the result in the cell you put this formula into.
=IF(ISNUMBER(MATCH(Sheet4!A1,Sheet5!$A$1:$A$10,0)),Sheet4!A1,NA())

Thanks for the reply back, but it didn't do as it should do. Its infact giving me the same o/p when the data in the cell matches. If it does not match, i am still getting #N/A. It should fetch the result from Sheet5 and place it in Sheet4 if the data does not match. Else, keep the same.

Appreciate any help/feedback.
Also, can we use VLookup function here? Any thoughts please?

 

[Jonmo1]

It should fetch the result from Sheet5 and place it in Sheet4 if the data does not match. Else, keep the same.

It's not exactly clear exactly what you want to happen if the data doesn't match.
You say "fetch the result form Sheet5" ?
From where in Sheet5, cell Z36 ?? G2 ??
Taking Vdors suggestion...

=IF(ISNUMBER(MATCH(Sheet4!A1,Sheet5!$A$1:$A$10,0)),Sheet4!A1,"Here is wehre you put the value you want if the data does not match")

 

[rahman4]

I think i got what i wanted. Thanks Jonmo1 and Vdor, for your help.
Here's what i did, and it gave me the result as expected..

=IF(ISNUMBER(MATCH(Sheet4!A1,Sheet5!$A$1:$A$10,0)),Sheet4!A1,Sheet5!A1)

Thanks again.

 

[Jonmo1]

You're welcome.

 

[rahman4]

Sorry to bother you again. Now that the above formula gets the data if it does not match. i.e. We are comparing apples-apples (Cell a1 in Sheet1 to Cell a1 in Sheet2). What if the matching data is present in Cell a15 in Sheet2?
Can we use any function that looks for the exact match in Sheet2, Cell a1:a10, and displays the exact match in Cell A1 of sheet2?

I hope i am clear with my question.

 

[Jonmo1]

No not really clear.
I'm actually more confused now re reading your post #3

=IF(ISNUMBER(MATCH(Sheet4!A1,Sheet5!$A$1:$A$10,0)),Sheet4!A1,NA())

If it does not match, i am still getting #N/A. It should fetch the result from Sheet5 and place it in Sheet4 if the data does not match.

The formula looks for the match in Sheet5.
If it didn't match, that means the data doesn't exist in sheet5.
So how can we fetch it from sheet5 if the formula proves it doesn't exist in Sheet5?

 

[rahman4]

No not really clear.
I'm actually more confused now re reading your post #3

The formula looks for the match in Sheet5.
If it didn't match, that means the data doesn't exist in sheet5.
So how can we fetch it from sheet5 if the formula proves it doesn't exist in Sheet5?

Here's the data, and it does exists in other sheet too. BUT, with some minor changes, like, Space between some words....(wild card characters). And if you look at these cells, they are present in Sheet5 but in different cells (

a2:b2, a3:b3, a8:b8, a10:b10) Please copy and paste it in a spreadsheet to know the difference..... Sheet4 Sheet5 Broadband Contracts.November2011 to November 2012 Broadband Contracts.November2011 to November 2012 Vendor.Contract Time.Overrides.USCurrent(Dates) Time.Overrides.USCurrent(Dates) Vendor.Contract Time..USJanuary2002toJuly2008 Time..USJanuary2002toJuly2008 Time.Schedule.USNovember2007toCurrent Time.Schedule.USNovember2007toCurrent Vendor.PurchaseOrders.Cu Vendor.PurchaseOrders.Cu Time..US November 2007 to Current Time..US November 2007 to Current Time.Schedule. Time.Schedule.USJanuary2002toJuly2008 UnemployeementClaims UnemployeementClaims Time.Schedule.USJanuary2002toJuly2008 Time.Schedule. <colgroup><col><col></colgroup><tbody> </tbody>

<tbody>
</tbody>

 

[Jonmo1]

and it does exists in other sheet too. BUT, with some minor changes, like, Space between some words....(wild card characters).

See that's just it, there's no such thing as "close enough" with the Exact Match Type Vlookup or Match function.
A single extra space means the values don't match. Period.
So if the Match or Vlookup is returning #N/A, that means the EXACT value doesn't exist on that sheet.
So there is no way for vlookup or match to then 'find' the value that in your mind is the same except for an extra space or character.

Now there have been some great threads on the forum about this using VBA solutions, they call it "Fuzzy Matching"
Use the forum search tool to look for "Fuzzy Match"
Those may prove usefull.