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

rahman4

New Member
Joined
Oct 9, 2014
Messages
6
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 spreadsheetExcel 2 spreadsheet
BroadbandContracts.November2011toNovember2012Broadband Contracts.November2011 to November 2012
Vendor.ContractsVendor.Contract
Time.Overrides.USCurrent(Dates)Time.Overrides.USCurrent(Dates)
Time.Schedule.Time..USJanuary2002toJuly2008
Time.Schedule.USNovember2007toCurrentTime.Schedule.USNovember2007toCurrent
Time.Schedule.USJanuary2002toJuly2008Vendor.PurchaseOrders.Cu
Time..USNovember2007toCurrentTime..US November 2007 to Current
Time..USJanuary2002toJuly2008Time.Schedule.
UnemployeementClaimsUnemployeementClaims
Vendor.PurchaseOrders.CuTime.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,
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Vidar

Well-known Member
Joined
Jul 19, 2012
Messages
1,254
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

New Member
Joined
Oct 9, 2014
Messages
6
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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Oct 9, 2014
Messages
6

ADVERTISEMENT

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.
 

rahman4

New Member
Joined
Oct 9, 2014
Messages
6

ADVERTISEMENT

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

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

New Member
Joined
Oct 9, 2014
Messages
6
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.....

Sheet4Sheet5
Broadband Contracts.November2011 to November 2012Broadband Contracts.November2011 to November 2012
Vendor.ContractTime.Overrides.USCurrent(Dates)
Time.Overrides.USCurrent(Dates)Vendor.Contract
Time..USJanuary2002toJuly2008Time..USJanuary2002toJuly2008
Time.Schedule.USNovember2007toCurrentTime.Schedule.USNovember2007toCurrent
Vendor.PurchaseOrders.CuVendor.PurchaseOrders.Cu
Time..US November 2007 to CurrentTime..US November 2007 to Current
Time.Schedule.Time.Schedule.USJanuary2002toJuly2008
UnemployeementClaimsUnemployeementClaims
Time.Schedule.USJanuary2002toJuly2008Time.Schedule.

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


<tbody>
</tbody>
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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.
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,109,204
Messages
5,527,407
Members
409,760
Latest member
zeeshansyed

This Week's Hot Topics

Top