• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Using Vlookup to Text String

AnggaRifandi

AnggaRifandi

New Member
Hi,


I have question about Vlookup() function. I want to show all data from my table using Vlookup, but the problem is the lookup value is text.


So I want to show data from my table using ex. 0061 as the lookup value, so I try to use =vlookup("*"&E12&"*";A2:C6;3;0) but the result is #N/A.


There is another solution to fix the problem?
 
Hi Angga,


your vlookup syntax is ok, if result is #N/A, then probably there is no string that fits your criteria in column A.
 
Hi, AnggaRifandi!

Would you please detail what values do you have in range A2:C6 and in E12, indicating type too? Thanks. Or consider uploading a sample file (refer to the second green sticky topic at this forums main page for guidelines).

Regards!
 
@SirBJ7

Seem I have a wrong data, so the result is not appear.


I have two question related with Vlookup() and string text.

I posted the question here.


https://docs.google.com/spreadsheet/ccc?key=0AhCF-jMpCuWJdFNsTVpxbnJyNm9hbmlzd1c5THhqcFE
 
Hi AnggaRifandi,


Kindly check this file. Hope it works.

http://dl.dropbox.com/u/60644346/vlookup_resolved.xls


@Hi SirJb7

Hi,

Just used Index(Match()) instead of vlookup, nothing else. :)


Regards,

Faseeh
 
For your question, the keep it simple method would be to create a helper column =concatenate(day1&day2) and use that helper column as your lookup. There are other options:


Using vlookup in a dynamic range.


http://dl.dropbox.com/u/1275899/question_vlookup_2.xlsx


I forget who I did the above for, but you'll see my explanation in blue. More or less, as long as the first part is ordered, you can use a dynamic range either specified at the formula level or named. That dynamic range becomes the table you're looking at.


For your second question, you'll want to use index and match:


=INDEX(A19:A23,MATCH(B34,B19:B23,0),1)
 
@ dan_l,


Hi,


I also used that helper column technique its really easy. Can't we create something that does it automatically like Match(00123,(--(A1:A10))&(--(C1:C10)),0) I never tried that?


Faseeh
 
Emmmm: I've never tried to do it with match like that either. Assuming angga is cool with keeping the list ordered though:


=VLOOKUP(RIGHT(B11,3),OFFSET($A$3,MATCH(LEFT(B11,3),A3:A9,0),1,COUNTIF(A3:A9,LEFT(B11,3)),4),3,FALSE)


Try that.


Aside, the whole dynamic range in your formula can be a bit of a beast to set up, but it's a cool trick to know.
 
Hi dan_l,


You formula gives #N/A Error whilst my formula in last post gives #Value Error..tried both of them..
 
Yeah. I fail:


=VLOOKUP(RIGHT(B11,3),OFFSET($A$3,MATCH(LEFT(B11,3),A3:A9,0)-1,1,COUNTIF(A3:A9,LEFT(B11,3)),4),3,FALSE)


Are you saying that the match formula wasn't working either?
 
Hi Faseeh ,


MATCH accepts a concatenated parameter ; thus if the value "002123" is to be used for matching :


=MATCH("002123",$A$3:$A$9&$B$3:$B$9,0)


entered as an array formula ( using CTRL SHIFT ENTER ) , will return 5 ; using this together with the INDEX function will return "MELON".


Even the first parameter "002123" can be a concatenated result ; thus if the text "002" is in cell F1 , and the text "123" is in G1 , you can use F1&G1 for the first parameter.


Narayan
 
Hi Faseeh ,


Welcome. I am just passing on whatever I keep learning !


As a footnote , I came across the following good links :


1. http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/comment-page-4/#comment-50401


2. http://msdn.microsoft.com/en-us/library/aa139976(v=office.10).aspx


Narayan
 
Hi all,


Thanks a lots friends. Your answers really help me.

Now I'm become more excited to learn Excel here.


@Narayank991

When we have to use array formula instead of regular formula?
 
Hi ,


If you are absolutely new to array formulas , start with any of the following :


1. http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx


2. http://spreadsheets.about.com/od/excelfunctions/ss/2011-03-23-excel-2010-multi-cell-array-formula-sbs-tutorial.htm


3. http://www.dummies.com/how-to/content/how-to-build-an-array-formula-in-excel-2010.html


4. http://www.cpearson.com/excel/ArrayFormulas.aspx


Chandoo has several articles on more advanced usages of array formulas :


http://chandoo.org/wp/tag/array-formulas/


If you have any specific issue on which you want support , come back to this forum.


Narayan
 
You must log in or register to reply here.
Back
Top