Jump to content


Photo

VLOOKUP using text string?

formulas VLOOKUP

  • Please log in to reply
4 replies to this topic

#1 Sweet_Intensity

Sweet_Intensity

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 16 February 2014 - 09:14 PM

Can one use a unique text string as the lookup value when using the VLOOKUP function? If possible, how is this done? Thanks.



#2 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 16 February 2014 - 11:02 PM

You can use text with the VLOOKUP function in exactly the same way as you would use a number.

 

For an in-depth explanation of the VLOOKUP function along with a video tutorial, see Expert Skills Lesson 3-22: Use a VLOOKUP function for an exact lookup.

 

If the lesson doesn't provide the explanation that you need, can you give more details and perhaps an example of the problem?


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#3 Sweet_Intensity

Sweet_Intensity

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 17 February 2014 - 07:24 PM

Thank you for your reply.

Here is the problem:

I used your Sales First Quarter 2008-3 data file to see if I could perform a VLOOKUP between two different workbooks.

The first version I left intact and I used this as the source workbook.

For the second version, I removed the Unit Prices (and the totals thus were removed), renamed it Sales First Quarter-3 without prices and this became the target workbook.

I performed a VLOOKUP function in the cell I2 as follows:=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$A:$J,9,FALSE) where G2 is the Product Name (unique text string) and the array is from the source workbook, position 9 (the I column) which is the Unit Price that I wish returned to the target workbook. This VLOOKUP returned a value of #N/A.

I then added a numerical index column to both the source and target workbooks by inserting a column before the date column, named 'Index" which had a numerical value incremented by one for each row.

Because I added a column, the price column  (which is the column that I wanted a return of value from) now became column J.

I performed a VLOOKUP function in the cell J2 as follows:=VLOOKUP(A2,'[Sales First Quarter 2008-3.xlsx]January'!$A:$K,10,FALSE) where A2 is a unique numerical value that I added, (thinking it might not be possible to use non-numerical Lookup-value), and the array is from the source workbook, position 10 (column J) which is the Unit Price value that I wished returned to the target workbook. This VLOOKUP computation returned the correct unit price value.

Can you help me determine why the text string lookup value returned the #N/A value and the numerical lookup value returned the correct value?

I very much appreciate your help.



#4 Jonathan

Jonathan

    Forums Administrator

  • Administrators
  • 886 posts

Posted 18 February 2014 - 04:53 PM

The problem is that VLOOKUP always looks in the first column of the range that you specify.

 

Your formula is:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$A:$J,9,FALSE)

 

The problem here is that you are telling your VLOOKUP function to look at columns A:J in the Sales First Quarter 2008-3 workbook.  VLOOKUP will look in the first column of the table to try to find the product name, so it is trying to find the product name in column A.

 

For a more in-depth explanation of how VLOOKUP works, I recommend viewing Expert Skills Lesson 3-22: Use a VLOOKUP function for an exact lookup.

 

To get this to work, your formula should be something like:

=VLOOKUP(G2,'[Sales First Quarter 2008-3.xlsx]January'!$G:$I,3,FALSE)

 

This formula looks in column G for the product name and then returns the unit price from column I.

 

When using VLOOKUP, it's usually better to create a table that's specifically designed for lookups.  I've attached a working example:

 

Attached File  Sales First Quarter 2008-3.xlsx   34.27KB   1553 downloads

xlookup1.png

 

Attached File  Sales First Quarter 2008-Prices.xlsx   10.02KB   1228 downloads

xlookup2.png

 

If you continue to have problems, please feel free to reply.


Jonathan is part of the professional team who answer Excel-related questions posted on the ExcelCentral.com forums.
Jonathan also tests our courses prior to publication and has worked on all of our ten world bestselling Excel books for Excel 2007, Excel 2010, Excel 2013, Excel 2016 for Windows and Excel 2016 for Apple Mac. Jonathan has also worked on over 850 video lessons for or video courses covering Excel 2007, Excel 2010 and Excel 2013.
As well as extensive Excel knowledge, Jonathan has worked in the IT world for over thirteen years as a programmer, database designer and analyst for some of the world's largest companies.


#5 Sweet_Intensity

Sweet_Intensity

    Newbie

  • Members
  • Pip
  • 3 posts

Posted 19 February 2014 - 01:38 AM

Thank you so much for your clear and easily understood explanation.  I am grateful.







0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users