Using trim & search function with vlookup

[KetanR]

Hi Guyz, please help me with the below so that i could crack the reconciliation.

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

 

[KetanR]

Hey, thanks for taking the time.

I will check with my company if they have got an add-in like you suggested.

True, if we are using the excel sheet we may have to use heavy array formulas and so in gdoc's but without arrays.

Macros are interesting, I also looked at the pivot table you created and that's exactly what iam after but my bad i don't really know how to create a macro so the only option i have is to give my company a working spreadsheet (GDoc)

 

[pleeseemailme]

I see now. You are not using Excel at all. You're trying to get all this two work inside a GoogleDoc, more specifically, a GoogleSheet, yes? I am not familiar with calculation time inside GoogleSheets. Google does have array formulas, so that might work. Is that the route we need to go?

Of course I would write the macro for you, if we went that way. But that means that you'd have to be using Excel and your company would have to allow macros to be enabled for each user using the workbook.(if you can use Excel but not macros, we can set it up for manual updating, but that is, of course, a bit more tedious each time) But, it sounds like all this has to take place inside the GoogleSheet, so this may not be an avenue to pursue at all. Let me know and I can work on the array formulas for you, or at least give you some references. But wait, isn't it already working for you with the FILTER function from the GoogleSheet?

Let me know what I can do to help. Thanks.

 

[KetanR]

@pleeseemailme

You are partly right. We do use excel but the data we have been discussing all this time are been recorded in GoogleSheets on daily basis. So for the part you can say we are not using Excel which is why trying to get all this to work inside a GoogleDoc. I was not knowing google could take array formulas but that's just perfect.

Right guess, data that we have been discussing in this thread takes place in the GoogleSheet.

Filter formula does work for me but replaces blanks with the data from another cell and that's the reason i could not proceed further. I will be glad if you could help me in fixing the issue or anything you could suggest me.

 

[KetanR]

@pleeseemailme

Hi There, Adding to my last reply.

If you remember, initially in this thread we discussed about the formula "=IF(VLOOKUP(A3,Sheet1!A:B,2,0)=VLOOKUP(A3,Sheet2!A:B,2,0)" whose result will be True if not False. Since today morning IST i have been trying to find a solution if i could get the exact result instead of True/False but could not find any or maybe am not aware of it.

So have you got any solution which could be of my help.

Thanks in advance.

 

[pleeseemailme]

Howdy,

I would imagine that in the TRUE argument you would simply place a formula similar to this: VLOOKUP(A3,Sheet2!,A:D,4,0). Just update "A:D,4" to whichever column you need the data from. Would that work?

Also, regarding the GoogleSheet Arrays, I've been to busy to build you something but here are two items to help you get started.

First, to set up an array formula, use: ARRAYFORMULA<bdo dir="ltr">([/FONT][FONT=Inconsolata]) [/FONT][FONT=microsoft sans serif]in the cell you want. [/FONT] [FONT=microsoft sans serif]Next, for compiling lists and skipping blanks please reference this page that helped me when I first was learning how to do this. [/FONT]</bdo>[URL="http://www.get-digital-help.com/2009/03/10/how-to-automatically-create-a-unique-list-and-remove-blanks/"]How to automatically create a unique distinct list and remove blanks | Get Digital Help - Microsoft Excel resource[/URL] and [URL="http://www.get-digital-help.com/2014/06/26/compare-two-lists-and-filter-unique-values-where-the-sum-in-one-column-doesnt-match-the-other-column/"]http://www.get-digital-help.com/2011/01/13/compare-two-lists-of-data-filter-records-existing-in-only-one-list-in-excel/[/URL] Although, while searching, I came across this which seems like it might work as well. [URL]https://www.extendoffice.com/documents/excel/2654-excel-dynamic-list-remove-blanks.html[/URL] I hope these can help you get started. Take and let me know.

 

[KetanR]

@pleeseemailme,

Mate,

Sorry one more "Q".

Interesting, I'll try the vlookup part with your suggested method but if iam using "<bdo dir="ltr">=IF(AND('Data Sheet'!E2>=Time!$C$4,'Data Sheet'!E2<=Time!$C$5),'Data Sheet'!A:A)=IF(AND(Sheet8!E2>=Time!$C$4,Sheet8!E2<=Time!$C$5),Sheet8!A:A)" </bdo><bdo dir="ltr">what changes do i have to make in the formula to get the result instead of True/False.


Thanks for sharing the references. For sure i will make use of it and i guess am already enjoying to learn.</bdo><bdo dir="ltr"></bdo>

 

[pleeseemailme]

I really enjoyed learning when my company gave me the chance. Still do, which is why I get on here every now and then.

To answer your question, I'll need to know what result you're trying to get. In your formula you state:

If [complicated formula] Is True Then

Do something​

Else

Do something Else
​

End If

By default if the last two arguments are left blank (the 'do somethings') then they just return TRUE and FALSE. If you replace TRUE with another formula, then it will return the result of the formula.

ie
IF(5=5) will return TRUE
IF(5=5,5+1,"") will return 6

This is just a simple example of the complex formula you have above.

 

[KetanR]

@pleeseemailme

That's exactly what iam doing which is why whenever i get stuck i keep disturbing you because i cant breathe till i crack it.

I got your point and the logic too.

In you open https://www.dropbox.com/s/1tzhlvskboyq11i/Sample Test.xlsx?dl=0 you can find the formula in Result!Cell B2, i have the result true/false but that's not what iam looking for. If data in both the sheets (Data Sheet & Data Sheet2) are a match then i want the result ie the data but not true and if its a mismatch only then go false. If has an error then i would be using IFERROR already.

 

[pleeseemailme]

Try this in Result!B2 and fill all the way down

=IF(AND('Data Sheet'!E2>=Time!$C$4,'Data Sheet'!E2<=Time!$C$5),'Data Sheet'!A2)

 

[KetanR]

@pleeseemailme

Tried your way but not working, i still get the result true instead of the ID No's.

If you open https://www.dropbox.com/s/1tzhlvskbo...Test.xlsx?dl=0

you can find the testing i have done in the result sheet.

Test 1 & 2 -Result Sheet - Cell B2 & C2

I don't know if my formula is correct but as you know based on the time buffer i created the time sheet. All i want the formula to do is,

1. To check if both the ID No's in Data Sheet & Data Sheet2 is a match.
2. If a match i want the ID No's as a result in another cell but not True.
3. If False only then the cell should flash false or else blank (If True then the ID No's).

Please advise.