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

 

[pleeseemailme]

I'm not sure I understand everything you need. Will there always only be 1 Jason? Are you looking to return all the matches of Jason on sheet 2? Just the first one? I don't know what the phrase "club the extract" means in this context. Could you clarify?

 

[KetanR]

@pleasemailme

For your Q1, No there shall be more names in Sheet1 which i want to match with details in Sheet 2. Below are the sample details for your reference.

=IFERROR(IF(VLOOKUP(B6,Sheet1 1!A:D,4,0)=VLOOKUP(B6,Sheet2!A:C,3,0)=True,"",(VLOOKUP(B6,Sheet2!A:C,3,0))),"")

B6 is the cell reference

Sheet1

1	A	B	C	D
2	ID NO	NAME	SHIPMENT DETAILS	CLIENT DETAILS
3	10	Vikky	00-10	VIKKY-VIKKY MAIK,AC-India
4	11	Ankit	00-11	ANKIT-ANKIT MAIK,AB-India
5	12	Mike	00-12	MIKE-MIKE CAND,AD-India
6	13	Jason	00-13	JASON-JASON BOURNE,SB-America
7	14	Austin	00-14	AUSTIN-AUSTIN PAUL,SB-America
8	15	Paul	00-15	PAUL-PAUL BLAKE,SB-America
9	16	Jake	00-16	JAKE-JAKE CHRIST,SB-America

<tbody>
</tbody>

Sheet2

1	A	B	C
2	ID NO	NAME	SHIPMENT DETAILS
3	10	Vikky	00-10
4	11	Ankit	00-11
5	12	Mike	00-12
6	13	Jason	00-13
7	14	Austin	00-14
8	15	Paul	00-15
9	16	Jake	00-16

<tbody>
</tbody>

Q2: I want to merge TRIM, SEARCH formula with VLOOKUP which is what I meant by "club the extract"

I hope this clarifies.

 

[KetanR]

@pleasemailme

Small change to the question

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

A6 is the cell reference

 

[pleeseemailme]

Howdy,
If I understand correctly, all you want is: If the name before the "-" from sheet 1 is in sheet 2, then return the name on sheet 2 to sheet 1, If not, return nothing.

If that is the case, this should work for you:

=IFERROR(OFFSET(Sheet2!$B$1,MATCH(MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1),Sheet2!$B$2:$B$8,0),0),"")

I used your sample data from above. That is where my cell references came from. I'd be curious to see how close I am to your original intent after you update the cell references.

 

[KetanR]

Greetings,

Thanks a lot for the formula.

=IFERROR(OFFSET(Sheet2!$B$1,MATCH(MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1),Sheet2!$B$2:$B$8,0),0),"")

I can tell you if its working or not only when you tell me where i could use "True" formula so that i could check if the data is correct and if yes the cell will go blank. If "FALSE" respective data of the cell will show up and that's how i had framed the formula which i mentioned in my initial post. :wink::wink:

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

 

[pleeseemailme]

I guess that's the part I'm not understanding. What data are you checking to see is correct? It looks you are are just checking if Vikki from sheet 1 is also in Sheet 2. If she is in both places, return a blank. If not, return Vikki's name. Is that not what you're trying to do? If not, I apologize for not understanding and will need further clarification. If so, then this modification to the above formula will work

=(IF(IFERROR(MATCH(MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1),Sheet2!$B$2:$B$8,0),-1)>0,"",MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1)))

It will return a blank if Vikki from sheet 1 is on sheet 2 and it will return Vikki if she is on sheet 1 and not on sheet 2.

 

[KetanR]

Hey come on apologies are not required anyways you are too close also partly right that iam just checking if Vikki from sheet 1 is also in Sheet 2. If she is in both places, return a blank. If not, return Vikki's name but i want you to help me add the extract formula in the below pasted formula,

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

because the below formula you suggested me i guess is to check particular coloumn from sheet 1 with respective coloumn in sheet 2 without range but the formula i referred checks on basis of the ID NO that has the range also checks if its right but all i have to do is apply the extract formula to it so help me out on the formula i created.

=(IF(IFERROR(MATCH(MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1),Sheet2!$B$2:$B$8,0),-1)>0,"",MID(Sheet1!D2,1,FIND("-",Sheet1!D2)-1)))

 

[pleeseemailme]

Perhaps I am over complicating this, then. If A6 is where the text string is with the dashes, then replace 'A6' in your formula with:

MID(Sheet1!A6,1,FIND("-",Sheet1!A6)-1)

I don't see how your formula used the ID field, but it seems to be working for you. Hopefully the above formula will extract the text you need for your VLOOKUP. If not, let me know and we'll try again. Thanks.

 

[KetanR]

@pleeseemailme

You are not complicating but trying to help me out anyways the formula you suggested is more or less the same as "=TRIM(LEFT(W4,SEARCH("-",W4)-1))" but it did work.

The formula works because iam doing a vlookup keeping ID as reference and trying to check if the data in another sheet is true.

I was trying to short the steps from 2-3 to 1 which is why i was trying to mix both the formulas together but its cool.

Thanks a ton.