VLookup

[HP_81]

Hi Excel Gurus,

I have a table from where i need to look up from Criteria tab.
I cannot get the desired result from Exact match nor Approximate match in Vlookup
Can someone help with this please.

 

[ETAF]

are you trying to exclude the -1 at the end of the text
if so
try
=INDEX(Criteria!A:A,MATCH(IFERROR(LEFT(A2,SEARCH("-",A2,1)-1),A2),Criteria!A:A,0))

otherwise not sure what you need, perhaps some examples of expected results and why would help

 

[HP_81]

Hi ETAF,

Thank you so much. I was trying to exclude -1 at the end of the text.
Is this also possible with vlook up ?

 

[ETAF]

yes
=VLOOKUP(IFERROR(LEFT(A2,SEARCH("-",A2,1)-1),A2),Criteria!$A$1:$A$20,1,FALSE)

if you do not want the N/A errors for either formula
then wrap in an IFERROR()

=IFERROR(VLOOKUP(IFERROR(LEFT(A2,SEARCH("-",A2,1)-1),A2),Criteria!$A$1:$A$20,1,FALSE),"")

=IFERROR(INDEX(Criteria!A:A,MATCH(IFERROR(LEFT(A2,SEARCH("-",A2,1)-1),A2),Criteria!A:A,0)),"")

 

[bosco_yip]

Or try this IFERROR+VLOOKUP function,

=IFERROR(VLOOKUP(LEFT(A2,FIND("-",A2&"-")-1),Criteria!$A:$A,1,0),"")

Regards

 

[HP_81]

Excellent...Thank you so much ETAF and bosco_yip.
Both the formula works perfect.
Really appreciate it.

 

[ETAF]

you are welcome

 

[HP_81]

Hi Excel Champs,

I have another one on Vlookup please.
How can i exclude the -1/2,2/2 and so on from sheet1 when looking up from Criteria tab.

I have attached a sample file..Thank you

 

[bosco_yip]

Hi Excel Champs,

I have another one on Vlookup please.
How can i exclude the -1/2,2/2 and so on from sheet1 when looking up from Criteria tab.

I have attached a sample file..Thank you

Open a new post for your new question.

This post is closed.

Regards