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
  • 887 posts

Posted 16 February 2014 - 11:02 PM

This article has moved

 

We have moved this article to our new site. The answer to this question can now be found here:

 

https://thesmartmeth...g-text-strings/


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
  • 887 posts

Posted 18 February 2014 - 04:53 PM

This article has moved

 

We have moved this article to our new site. The answer to this question can now be found here:

 

https://thesmartmeth...g-text-strings/


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