So i need to do a vlookup type function, where i need to search for a key in an array, if i find it, spit out the value.
For the array i'm using a table on sheet 2.
On sheet one Col A is the string I want to search, col B is where I would want the category to show.
Sheet two has col A with the Key (Merchants) and I want to show the Category which is Col B
See attached Workbook.
I used a VLOOKUP function combined with an IF statement to yield "NO CATEGORY" when an item isn't in the list.
Formula fragments follow from Sheet 1, cell A2:
VLOOKUP(A3,Sheet2!$A$2:B$15,2) =IF(VLOOKUP(A3,Sheet2!$A$2:B$15,2)=0,"NO CATEGORY",VLOOKUP(A3,Sheet2!$A$2:B$15,2))
HINT: If you want to expand the LOOKUP TABLE, DO NOT add another item at the end.
Insert a line inside the LOOKUP TABLE boundaries, and the formulas will automatically
adjust themselves. If you need to add a LAST ITEM, insert a row before the last item,
'cut and paste' the last row to the newly inserted row, and then add the text on the
LAST LINE.
Thanks LJ that's what I had before and doesn't work. It doesn't search substrings.Originally Posted by LJMetzger
See attached Workbook.
I used a VLOOKUP function combined with an IF statement to yield "NO CATEGORY" when an item isn't in the list.
Formula fragments follow from Sheet 1, cell A2:
VLOOKUP(A3,Sheet2!$A$2:B$15,2) =IF(VLOOKUP(A3,Sheet2!$A$2:B$15,2)=0,"NO CATEGORY",VLOOKUP(A3,Sheet2!$A$2:B$15,2))
HINT: If you want to expand the LOOKUP TABLE, DO NOT add another item at the end.
Insert a line inside the LOOKUP TABLE boundaries, and the formulas will automatically
adjust themselves. If you need to add a LAST ITEM, insert a row before the last item,
'cut and paste' the last row to the newly inserted row, and then add the text on the
LAST LINE.
Hi edsters,
I didn't think it did substrings either, but in the sample Workbook I posted, it WORKED, as long as the items in 'Sheet 1' are 'Superstrings' of the items in 'Sheet 2'.
For example if sheet 1 contains:
a. If you type 'EVERGREEN COMPUTERS AND PRINTERS' the category is 'HOME OFFICE'.
b. If you type 'EVERGREEN COMPUTER' the category is 'fuel' (the previous entry in the lookup table).
c. If you type 'EVERGREEN COMPUTERx' the category is 'HOME OFFICE' (the previous entry in the lookup table).
When you use VLOOKUP with parameter 4 'True', 1, or omission is supposed to return an APPROXIMATE match that is the way VLOOKUP is supposed to work. When the 4th parameter is 'False or 0', then VLOOKUP needs an EXACT MATCH.
NOTE: The items in the Lookup Table in 'Sheet 2' should be alphabetical (which you have).
I agree that benishiryo's post is superior to mine.
Lewis
Last edited by LJMetzger; 10-14-2013 at 08:34 AM. Reason: Corrected errors VLOOKUP description (had logic backwards).
That was my understanding as well LJMetzger however as I am creating a sheet for my finances, I couldn't have them categorized incorrectly.
Your help however was greatly appreciated!
welcome to the forum, edsters. is this what you need?
=IFERROR(LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$15,A1),Sheet2!$B$1:$B$14),"")
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thanks Benishiryo that works perfectly, by anychance can you explain the 2^15 part of your formula. I wish to get a better understanding of the formula for future use.
@edsters:
SEARCH will return the position number of the substring. the maximum number of characters possible in Excel 2007 & above is 32767. 2^15 returns 32768. like the approximate match in VLOOKUP, it searches for the exact number. if it doesn't find one, it goes back to the number smaller than 32768
@Lewis:
just a correction. FALSE or 0 is supposed to return an EXACT match. TRUE, 1 or omission is supposed to return an APPROXIMATE match. you are right that it must be sorted for the latter to work, but it's used mostly in the case of numbers & not texts like this.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks