I need to make my Excel spreadsheet lookup the values in column A, and find the corresponding value in column B (in another worksheet in the same workbook). So I used the LOOKUP (vector option), and thought it was working great.
But I just noticed what LOOKUP does when it can’t find a match: it returns the value from column B for the nearest value in column A, lower than the key you were searching for.
First, I thought this was an incredibly stupid design decision on the part of Microsoft. I don’t want something reasonably close; I want a match, dammit. If you can’t find a match, tell me. Don’t give me the wrong answer and hope that’ll work for me.
So if I can’t guarantee that my lookup table will have all possible values for column A, what do I do?
Inigo, thanks for pointing me in the right direction. Having the True/False parameter in vlookup is definitely what I need.
However…and I don’t want you to debug my formula for me, but…I’m getting nothing but #REFs. My syntax must be messed up.
So let’s get a bit more specific. My workbook has two sheets – let’s call them Data and Lookup.
Data has 2 columns; the value I’m looking up, in column C; and the corresponding value I want to fill in, in column D.
Lookup has 2 corresponding columns: A (the value being searched) and B (the value I want to return).
So in a cell in Data!D, I should enter =vlookup(C1,Lookup!A1:A10,2,False), right? “2” because the formula seems to want a numeric offset for the column containing the value.
A2 is the value you’re looking up
Sheet2!A1:B4 is the name of the sheet you’re referencing and the range of data
2 is the column of the range of data (2nd column in my example)
0 is an exact match, if you put 1 there it would return closest match
aHA! And what if there are 2 values I’d like returned (in columns B and C) in Lookup? Does vlookup only work with pairs of data–the data, and one value to return.
Inigo Montoya is correct. VLOOKUP is the correct function. LOOKUP is obsolete. The FALSE part is critical in the formula and it is the remedy to what you are directly asking about.
It isn’t stupid on Microsoft’s part. There are uses for the nearest match but most people don’t use it that way. VLOOKUP with a match value of FALSE is what you and most people need. I work for a mega-corp and I have spent dozens of hours in a classroom setting teaching people how to use lookup functions. The best way to learn is to start very small by building your own, simple data, and then experimenting with it. It isn’t easy at first glance but it will make sense quickly if you do that.
No worries. I fiddled with VLOOKUP on and off for days when I first had to use it, trying to figure it out and trying to come up with any number of mechanics I could use instead. Never be afraid to ask ‘just one more’ thing.
Excel rocks. At some point you’ll find that they didn’t create a function for what you need to do. You can then go about learning how to define your own functions. The mind boggles.
You can create one range of multiple columns and reference each column as needed. If you wanted to return information from both columns you would have to write a formula to combine the information.
Example: you have data in Sheet2 in 3 columns as follows and want to look up a name and display the grade and course.
The formula would look like this:
=IF(ISERROR(VLOOKUP(A2,Sheet2!A1:C4,2,0)),"",(VLOOKUP(A2,Sheet2!A1:C4,2,0)&" "&(VLOOKUP(A2,Sheet2!A1:C4,3,0))))
Breaking it down as follows:
=IF(ISERROR------- If the following formula returns an error then display “”. (anything between quotation marks will display as text so in this case I’m asking it to display nothing. you could put “missing data” and that would show up if there wasn’t any data to match in the table. This keeps the formula from returning an error. This can be useful if you need to return a number to avoid errors associated with further manipulation of the data.
VLOOKUP(A2,Sheet2!A1:C4-------- lookup whatever is in cell A2 from the range A1:C4 in Sheet2.
2---- column 2 of sheet2
0----- exact match only. A 1 would return closest match
The remainder of the formula is the remainder of the If statement. If there is no error then it will return information from column 2 Sheet2, then add a couple of blank spaces (" "), and then add whatever is in column 3 of Sheet2.
You can past the above formula in cell B2 of Sheet one and creat your data in Sheet 2 to test this.