Using trim & search function with vlookup

K

KetanR

New Member
Joined
Feb 14, 2017
Messages
32
Hi Guyz, please help me with the below so that i could crack the reconciliation.:confused::eek:

Data in one cell of Sheet 1: JASON-JASON BOURNE,SB-America
Data in one cell of Sheet 2: JASON

I want to search and trim the word before hyphen without any consistency ie. only "JASON" from the Sheet 1 and want to match if the data of Sheet 2 is true using Vlookup formula which i have pasted below for your reference.I have been using the below formula to trim and search but not sure how to club the extract with vlookup formula as like the one pasted below the trim formula.

=TRIM(LEFT(W4,SEARCH("-",W4)-1))

=IFERROR(IF(VLOOKUP(B6,Sheet1 1!P:AC,14,0)=VLOOKUP(B6,Sheet2!A:G,7,0)=True,"",(VLOOKUP(B6,Sheet2!A:G,7,0))),"")


Thanks in advance
 
Sort by date Sort by votes
Wait. So you're matching the IDs and then checking to see if the names are the same? I've been going from the perspective that you are just matching and checking names. I guess because I don't know what data is in your columns D and C for your VLOOKUP. If column A is the ID field and D and C are names - then you need to Trim the text string in Sheet 1 column D and see if it matches the text (already trimmed, per say) in sheet 2 column C. Is this also correct?

If that is the case then, from your formula above, replace W4 with VLOOKUP(A6,Sheet1 1!A:D,4,0). In other words, I think this will do it:


=IFERROR(IF(TRIM(LEFT(VLOOKUP(A6,Sheet1 1!A:D,4,0),SEARCH("-",VLOOKUP(A6,Sheet1 1!A:D,4,0))-1))=VLOOKUP(A6,Sheet2!A:C,3,0)=True,"",(VLOOKUP(A6,Sheet2!A:C,3,0))),"")
or

=IFERROR(IF(TRIM(LEFT(VLOOKUP(A6,Sheet1!A:D,4,0),SEARCH("-",VLOOKUP(A6,Sheet1!A:D,4,0))-1))=VLOOKUP(A6,Sheet2!A:C,3,0)=TRUE,"",(VLOOKUP(A6,Sheet2!A:C,3,0))),"")

this second one replaces Sheet 1 1 with Sheet1 in case we've made a typo on the sheet names along the way. I hope this does it!
 
Upvote 0

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
@pleeseemailme

Hi there,

Perfect, we are now on the same page.

Yes you are absolutely right, Iam trying to match the IDs and then checking to see if the names entered in both the sheets are same.

OMG what a silly mistake, you caught me. I guess have made the data's in a bit of hurry which is why you got confused but i have made the correction. Now i guess it should be right. Sorry for the confusion.

CORRECTED FORMULA:

<bdo dir="ltr">=IFERROR(IF(TRIM(LEFT(VLOOKUP(A:A,Sheet2!A:D,4,0),SEARCH("-",VLOOKUP(A:A,Sheet2!A:D,4,0))-1))=VLOOKUP(A:A,Sheet1!A:B,2,0)=True,"",(VLOOKUP(A:A,Sheet1!A:B,2,0))),"")</bdo>


Sheet1


1ABC
2ID NONAMESHIPMENT DETAILS
310JASON00-10
411ANKIT00-11
512MIKE00-12
613VIKKY00-13
714AUSTIN00-14
815PAUL00-15
916JAKE00-16

<tbody>
</tbody>


Sheet2

Formula is in Cell E3

1ABCD E
2ID NONAMESHIPMENT DETAILSCLIENT DETAILS RESULT
310Vikky00-10VIKKY-VIKKY MAIK,AC-India JASON
411Ankit00-11ANKIT-ANKIT MAIK,AB-India
512Mike00-12MIKE-MIKE CAND,AD-India
613Jason00-13JASON-JASON BOURNE,SB-America VIKKY
714Austin00-14AUSTIN-AUSTIN PAUL,SB-America
815Paul00-15PAUL-PAUL BLAKE,SB-America
916Jake00-16JAKE-JAKE CHRIST,SB-America

<tbody>
</tbody>

Now, i want another favor from you.

Sheet1

1ABC
2 ID NONAMESHIPMENT DETAILS
310Jason00-10
411Ankit00-11
512Mike00-12
613Vikky00-13
714Austin00-14
815Paul00-15
916Jake00-16

<tbody>
</tbody>


Sheet2

1ABCDE
2ID NONAMESHIPMENT DETAILSCOMPANY DETAILSRESULT
310Vikky00-10MGM RESORTS
411Ankit00-11ITC
512Mike00-12TAJ
613Jason00-13RESIDENCY
714Austin00-14BLUEBAY
815Paul00-15GOLDEN RESORTS
916Jake00-16HYATT

<tbody>
</tbody>

Until now what we did was to extract and match but now planning only to match & check if both the sheets have the same names keeping ID NO'S as the base and if yes, result should flash but if meets a condition and the condition is, if Sheet1!Col B & Sheet2!Col B of the respective ID's has the name Vikky only then Sheet2!Result Coloumn E:E should flash Vikky or else blank.

Result Ex:

1ABCDE
2ID NONAMESHIPMENT DETAILSCLIENT DETAILSRESULT
310Vikky00-10VIKKY-VIKKY MAIK,AC-IndiaVikky
411Ankit00-11ANKIT-ANKIT MAIK,AB-India
512Vikky00-12MIKE-MIKE CAND,AD-IndiaVikky
613Jason00-13JASON-JASON BOURNE,SB-America
714Vikky00-14AUSTIN-AUSTIN PAUL,SB-AmericaVikky
815Paul00-15PAUL-PAUL BLAKE,SB-America
916Vikky00-16JAKE-JAKE CHRIST,SB-AmericaVikky

<tbody>
</tbody>


=if((VLOOKUP(A3,Sheet1!A:B,2,0)="Vikky")=if(VLOOKUP(A,Sheet2!A:B,2,0)="Vikky","Vikky")," ",if(VLOOKUP(A3,Sheet2!A:B,2,0)="Vikky","Vikky"))

I tried a lot but somehow i guess the above formula is wrong so save me.
 
Upvote 0
Howdy,
I'm glad we go the first issue resolved.

I see nothing wrong with the setup of the formula for the new request. There is a reference missing, which I've replaced here

=IF((VLOOKUP(A3,Sheet1!A:B,2,0)="Vikky")=IF(VLOOKUP(A3,Sheet2!A:B,2,0)="Vikky","Vikky")," ",IF(VLOOKUP(A3,Sheet2!A:B,2,0)="Vikky","Vikky"))

If this does not work I am going to need more specific details about the data. I can't use the sample data provided because the result data doesn't match the samples. Could you let me know what is on each sheet, and which sheet and cells the results are in. It looks like it might be sheet2 but I'm not sure. Then, could you send a set of sample result data based on the sample data?

Let me know, thanks.
 
Upvote 0
Greetings,

I would not have cracked it without your help. Thank you.

Guess what, the second issue too got resolved and works just perfect. Thanks again.


Am back with another query ;)

As you know, am trying to match the IDs and then checking to see if the names entered in both the sheets are same and so goes on with the other data.

Other than FILTER, TRIM, SEARCH, IF, IFERROR i have been using VLOOKUP formula to match and check. As a result my sheet keeps hanging or takes too much time.

Only way i can make it easy and smooth is by replacing the below VLOOKUP formula with both INDEX & MATCH.

=IF(VLOOKUP(A3,Sheet1!A:B,2,0)=VLOOKUP(A3,Sheet2!A:B,2,0)=True,"",(VLOOKUP(A3,Sheet2!A:B,2,0)))

Can you help me with the above formula replacing VLOOKUP with MATCH & INDEX so that i could check if data in both the sheets are either TRUE OR FALSE and if neither both then cell goes blank.


Thanks in advance.
 
Upvote 0
Oh, glad to hear that you got it to work. I'm just now getting a break to look at this again. If I may, I'd like to still offer my 2 cents. While the VLOOKUP function is robust, the function in and of itself isn't what's necessarily contributing to your calculation time. Both VLOOKUP and INDEX are non-volatile functions, which means they are only recalculated when something triggers them to calculate. It sounds like you are already aware that some functions are volatile, or they recalculate whenever ANYTHING changes in a workbook. You can read more about this here: Volatile Excel Functions -Decision Models

What I was going to recommend to reduce calculation time is to replace your massive ranges (ie, A:B for all of column A and B) with dynamic named ranges. If you're unfamiliar with ranges, this article here is excellent for an overview: http://www.contextures.com/xlNames01.html

For a more in depth review of dynamic ranges, I highly recommend this article here: https://exceljet.net/formula/dynamic-named-range-with-offset

Basically, I suspect your slow calculation time is due to Excel ruffling through entire columns for each formula you have in your workbook. Replaced these with dynamic ranges will significantly reduce the number of cells Excel will cycle through while extracting and matching your data.

I hope this is helpful, let me know!
 
Upvote 0
@pleeseemailme

That's absolutely no problem. Thank you.

If you remember from my last reply, I said i cracked but you were right that even if i replace vlookup formulas with Index & Match i would still face the slowness infact iam. so now planning to recreate so you gotta help me. Below pasted are the steps that i perform during which will let you know where iam stuck.

Below is my data sheet which i have it in Sheet2

1ABCDEF
2ID NONAMESHIPMENT DETAILSCLIENT DETAILSCONSIGNMENT TIMEINSTRUCTIONS
310Vikky00-10VIKKY-VIKKY MAIK,AC-India2/20/2017EAST
411Ankit00-11ANKIT-ANKIT MAIK,AB-India2/21/2017WEST
512Mike00-12MIKE-MIKE CAND,AD-India2/20/2017WEST
613Jason00-13JASON-JASON BOURNE,SB-America2/20/2017EAST
714Austin00-14AUSTIN-AUSTIN PAUL,SB-America2/22/2017EAST
815Paul00-15PAUL-PAUL BLAKE,SB-America2/22/2017EAST
916Jake00-16JAKE-JAKE CHRIST,SB-America2/25/2017EAST
1017Anil00-17ANIL-JAKE CHRIST,SB-America2/21/2017EAST
1118Kite00-18KITE-JAKE CHRIST,SB-America2/21/2017EAST
1219Kirsten00-19KIRSTEN-JAKE CHRIST,SB-America2/25/2017EAST
1320Mangi00-20MANGI-JAKE CHRIST,SB-America2/21/2017WEST
1421Sean00-21SEAN-JAKE CHRIST,SB-America2/21/2017WEST
1522Laura00-22LAURA-JAKE CHRIST,SB-America2/25/2017WEST
1623Sew00-23SEW-JAKE CHRIST,SB-America2/22/2017WEST
1724Stewart00-24STEWART-JAKE CHRIST,SB-America2/25/2017WEST
1825Harsa00-25HARSA-JAKE CHRIST,SB-America2/24/2017WEST
1926Kirsten00-26KIRSTEN-JAKE CHRIST,SB-America2/24/2017WEST
2027Danile00-27DANILE-JAKE CHRIST,SB-America2/24/2017WEST
2112Keil00-12KEIL-MIKE CAND,AD-India2/20/2017WEST
2213Krish00-13KRISH-JASON BOURNE,SB-America2/20/2017EAST
2314Carl00-14CARL-AUSTIN PAUL,SB-America2/22/2017EAST
2415Paul00-15PAUL-PAUL BLAKE,SB-America2/22/2017EAST

<tbody>
</tbody>

STEP 1: I enter start date in cell B2 and end date in cell B3
NOTE: Below table is in Sheet5

1AB
2START DATE2/20/2017
3END DATE2/24/2017

<tbody>
</tbody>

STEP 2: After Step 1, With help of the formula "<bdo dir="ltr">=IF(AND(Sheet2!E3>=$B$2,Sheet2!E3<=$B$3),Sheet2!B:B,"")" in Cells A2,B2,C2,D2,E2 & F2. I get the below data but one column at a time.
NOTE: Below table is in Sheet5

</bdo>
1ABCDEF
2ID NONAMESHIPMENT DETAILSCLIENT DETAILSCONSIGNMENT TIMEINSTRUCTIONS
310Vikky00-10VIKKY-VIKKY MAIK,AC-India2/20/2017 0:00:00EAST
411Ankit00-11ANKIT-ANKIT MAIK,AB-India2/21/2017 0:00:00WEST
512Mike00-12MIKE-MIKE CAND,AD-India2/20/2017 0:00:00WEST
613Jason00-13JASON-JASON BOURNE,SB-America2/20/2017 0:00:00
714Austin00-14AUSTIN-AUSTIN PAUL,SB-America2/22/2017 0:00:00EAST
8
916Jake00-16JAKE-JAKE CHRIST,SB-America2/25/2017 0:00:00
1017Anil00-17ANIL-JAKE CHRIST,SB-America2/21/2017 0:00:00EAST
11
1219Kirsten00-19KIRSTEN-JAKE CHRIST,SB-America2/25/2017 0:00:00EAST
1320Mangi00-20MANGI-JAKE CHRIST,SB-America2/21/2017 0:00:00
14
1522Laura00-22LAURA-JAKE CHRIST,SB-America2/25/2017 0:00:00WEST

<tbody>
</tbody>
<bdo dir="ltr">
</bdo>STEP 3: After Step 2, just to exclude blanks and consolidate the step 2 data to have it in a proper order i use <bdo dir="ltr">=IFERROR(FILTER(Sheet5!E2:E15,Sheet5!E2:E15<>""),"") in Cells A2,B2,C2,D2,E2 & F2.
Now the problem is it does exclude the blanks but replaces the cell with the next cell data which will throw a mismatch whenever i perform a reconcile.
NOTE: Below table is in Sheet5

</bdo>
1ABCDEF
2ID NONAMESHIPMENT DETAILSCLIENT DETAILSCONSIGNMENT TIMEINSTRUCTIONS
310Vikky00-10VIKKY-VIKKY MAIK,AC-India2/20/2017 0:00:00EAST
411Ankit00-11ANKIT-ANKIT MAIK,AB-India2/21/2017 0:00:00WEST
512Mike00-12MIKE-MIKE CAND,AD-India2/20/2017 0:00:00WEST
613Jason00-13JASON-JASON BOURNE,SB-America2/20/2017 0:00:00EAST
714Austin00-14AUSTIN-AUSTIN PAUL,SB-America2/22/2017 0:00:00EAST
816Jake00-16JAKE-JAKE CHRIST,SB-America2/25/2017 0:00:00EAST
917Anil00-17ANIL-JAKE CHRIST,SB-America2/21/2017 0:00:00WEST
1019Kirsten00-19KIRSTEN-JAKE CHRIST,SB-America2/25/2017 0:00:00
1120Mangi00-20MANGI-JAKE CHRIST,SB-America2/21/2017 0:00:00
1222Laura00-22LAURA-JAKE CHRIST,SB-America2/25/2017 0:00:00

<tbody>
</tbody>
<bdo dir="ltr">
After this, all iam trying to do now is to put both the </bdo><bdo dir="ltr">=IF(AND(Sheet2!E3>=$B$2,Sheet2!E3<=$B$3),Sheet2!B:B,"") and =IFERROR(FILTER(Sheet5!E2:E15,Sheet5!E2:E15<>""),"") formulas together to perform simultaneously and pull the data i need but the condition will be that if a cell is blank stays the way it is but will exclude if the whole row is blank. With this condition data stays cool.

However, will it be possible to pull data from A:F using =IF(AND(Sheet2!E3>=$B$2,Sheet2!E3<=$B$3),Sheet2!B:B,"") instead of using one cell reference at a time, if so please advise.

Mate, You gotta help me to sort this issue because if we crack this i may not have to use neither vlookup, index nor match.

I thought of attaching a sample sheet for your reference but could not find the option.

</bdo>
 
Last edited:
Upvote 0
Howdy there, sorry for the delay. It's been busy over here. After trying to make my own sample sheet, I think it would be best for me to get a copy of your sample sheet (complete with the formulas and everything). The best way to do this is to upload the file to a shareable site where I can download it. Dropbox, Google Drive, OneDrive, any of these will work and you're identity can still be relatively secret this way. You can even send me a direct message with the link so it isn't as public if that's a concern. What do you think, could you get me a test-data file?
 
Upvote 0
Thanks. I got the workbook and just as I suspected. I don't have use of the FILTER formula. Your company must have an add-in that applies a user defined function called FILTER to the data. Can you verify this?

Filtering data with blanks is a problem I had to overcome when designing templates for my company several years ago. I have 2 solutions. One that uses powerful, calculation heavy Array Formulas and one that uses pivot tables, much less calculation heavy. The difference is that the formulas will update automatically, while the pivot table will need to be manually refreshed. Does one seem more appealing than the other? Either one will need to have an additional sheet beyond the Extract sheet to remove the blanks.

Although, if you're not opposed to macros, we can create a pivot table from the 'Data Sheet' data that will update when a button is clicked and give you the data you need, no formulas or heavy calculation needed. Take a look a the pivot table here and see if it's what you're after.
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
New error with TRIM function
Replies
3
Views
819
Joe4
Joe4
M
  • Question
Keep addresses with apartment numbers using a wildcard
Replies
0
Views
340
Myke50701
M
C
  • Question
using vlookup on multiple sheets VBA (results in different columns)
Replies
4
Views
3K
DanteAmor
DanteAmor
redspanna
  • Question
search for date match through date range using VLOOKUP
Replies
3
Views
580
arthurbr
arthurbr
H
  • Question
Vlookup formula to take systems month and date and search in sheet1 for same month and year and retrive adjacent cell value
Replies
7
Views
908
Fluff
Fluff

Forum statistics

Threads
1,215,473
Messages
6,125,020
Members
449,203
Latest member
tungnmqn90

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top