Calc: help with VLOOKUP

users
#1

Something is not working for me. Hope someone can help.

I have a two-sheeted speadsheet.

Sheet 1 has three columns as follows:
  A. Name
  B. Price (ignorable as far as this example is concerned)
  C. ID number

Sheet 2 has three columns as follows:
  A. Item Code
  B. Item description
  C. Minimum bid (ignorable as far as this example is concerned)

The operator will be given a piece of paper with an Item code, an ID
number and a price. I would like him to be able to look up an Item code
in Sheet 2 and then, in that row, enter the ID number (column D?). A
VLOOKUP formula would paste the Name into an available column (E?) and
the operator would enter the price into the next column (F?).

Where do I type the =VLOOKUP formula?

Can I save the sheet with the formula so I can set this strategy up
ahead of time?

How do I get the formula to apply properly to every row?

What should the initial formula look like?

Thank you.

#2

The first problem I see is that your lookup value -- in this case the ID number -- has to be the first column in the lookup array.

Using your specific example, the VLOOKUP function would be used in column E of Sheet 2. If you were wanting to also copy the price from Sheet 1 (which you've said you're not), you would need another VLOOKUP in Column F.

This can be set up in advance, but you'll need to use an exact lookup unless you know you exact lookup range ahead of time (in which case you don't need to worry about searching an empty area).

I know this didn't answer all your questions, but others will chime in.

Dave

#3

Dave Liesse has written on 2/10/2014 4:00 PM:

The first problem I see is that your lookup value -- in this case the ID
number -- has to be the first column in the lookup array.

Interesting. I don't think I've ever seen that stated.

Using your specific example, the VLOOKUP function would be used in
column E of Sheet 2.

So I type the VLOOKUP into a cell in E. Where do I enter the ID code?

This can be set up in advance, but you'll need to use an exact lookup
unless you know you exact lookup range ahead of time (in which case you
don't need to worry about searching an empty area).

??? Is there a problem if I overstate the size of the lookup area???

Thanks.

#4

OK, the item that you want to look up to return the price or name needs to be in both spreadsheets (otherwise you won't find a match!). IF (big IF here) the ID# and item code are the same for each item, then this could be done as follows:

Database looks like this:
Sheet 1:
ID Name Price ID
1 Shirt 6 1
2 Pants 5 2
3 Hat 3 3
4 Gloves 2 4

Sheet 2:
Code Description Min. Bid
3 ;lkajsdfk ;aljsdf 2
3 ;lkjdf 2
4 ;akldjf ;laksf ;alskdfj 1
2 oisel 3
1 ;lakjsdf 4
1 woe 4
4 ldklw 1

(Gibberish was intentional. I didn't want to bother with actual descriptions.) In this case, take the code you're given (3 in the first line, sheet 2) and look up the value in sheet 1 (in this case it would be "Hat"). To do this:

Sheet 2, add the following column in D: =VLOOKUP(A2,Sheet1.$A$1:$D$5,2,0)
     copy and paste this formula as far down the sheet as you need to. Note the dollar symbol is needed to create a static reference to the array that you are looking at for the lookup. Otherwise it starts at the row you're looking at and goes down from there, meaning eventually you'll almost certainly NOT find a match. The lookup value (A2 in the first cell of D2) will change for each row the formula is pasted into.

Final sheet 2 would look like this:

Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves

In Column D, you would see the item name returned, but the actual contents of that column would be the formula above, with the first part after the vlookup (A2) changing with each row from A2 to A3, A4, A5, A6, A7 and A8. What this does is looks up whatever is in A1 and returns the name based on what it matches for ID in sheet 1.

Read carefully and I think you'll eventually get it. It can be confusing, but it makes sense if you're careful to follow the logic.

Now, after the 7 lines of sample data above, I copied and pasted the vlookup formula into the rest of Column D (as far as you think you need to go). That's the setup. After that, when a number (from 1-4 for the item ID in sheet 1) is typed into column A, an item name is returned in Column D. I typed a bunch of numbers into Column A to illustrate below. See:
Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
3
  
  Hat
4
  
  Gloves
4
  
  Gloves
4
  
  Gloves
1
  
  Shirt
1
  
  Shirt
2
  
  Pants
2
  
  Pants
2
  
  Pants

  #N/A

  #N/A

  #N/A

  #N/A

  #N/A

Note that the #N/A values in Column D are columns for which no "code" has been entered yet. The formula can't return a value since there's nothing in the lookup column A. Also note that we didn't try to lookup the Description for Column B (that data doesn't exist in Sheet 1 so it wouldn't work anyway). We also didn't try to look up the price (column C of sheet 1) but could have done that with a new lookup formula in a column E in sheet 2.

I hope this makes sense. I can send you the sample I created (or post to Nabble if I can get the time to do this...). Let me know if you want me to send you the sample file directly (I won't do so until I hear from you). If others want to see it too, let me know and I'll just post to Nabble (I don't think I have an account yet, but will figure it out).

Carl

#5

Hmmm. Formatting leaves a little to be desired. Let's see:

1st sheet:
Database looks like this:
Sheet 1:
ID Name Price ID
1 Shirt 6 1
2 Pants 5 2
3 Hat 3 3
4 Gloves 2 4

Sheet 2:
Code Description Min. Bid
3 ;lkajsdfk ;aljsdf 2
3 ;lkjdf 2
4 ;akldjf ;laksf ;alskdfj 1
2 oisel 3
1 ;lakjsdf 4
1 woe 4
4 ldklw 1

Sheet 2 would look like:

Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves

Final sheet:

Code Description Min. Bid Item Name
3 ;lkajsdfk ;aljsdf 2 Hat
3 ;lkjdf 2 Hat
4 ;akldjf ;laksf ;alskdfj 1 Gloves
2 oisel 3 Pants
1 ;lakjsdf 4 Shirt
1 woe 4 Shirt
4 ldklw 1 Gloves
3 Hat
4 Gloves
1 Shirt
#N/A

HTH clarify. Again, I can post this if it helps.

Carl

#6

Dave Liesse has written on 2/10/2014 4:00 PM:

The first problem I see is that your lookup value -- in this case the ID
number -- has to be the first column in the lookup array.

When I moved the ID number to the first column, my VLOOKUP worked! Where
does it say that that is a requirement?

#7

If you look at the help for VLOOKUP, the second sentence says "This function checks if a specific value is contained in the first column of an array."

Dave

#8

Take a look at the Help entry for vlookup. That entry begins with the first 3 lines:

Vertical search with reference to adjacent cells to the right. This function checks if a specific value _is contained in the first column of an array_. The function then returns the value in the same row of the column named by Index.

Underlining added. In other words, it looks for values in the first column of the named array, then returns the value in the same row in whatever column you tell it to (the "Index" column). I'm sure it could be stated more clearly and more specifically in the details of the Help entry, but it's there.

A few hints for vlookups: I find I usually want to add "hard" references for the array so that it always looks at the intended rows of the array. To do that, you need to add $ before each cell reference in the array. So if the array of the function is A1:C100, I enter it as $A$1:$C$100. This forces vlookup to always look at those cells regardless of what row the calculation is happening on.

Without this fixed reference, as the vlookup moves to new cells (e.g. down a column) the array will also shift down. So for example, if the looked up value is in cell D1 and the vlookup formula is in E1, the above array will be searched. But if you then copy that function down column E, each cell you move down will shift the Array down as a relative reference. That is, on line E2, the vlookup will look at D2, then search for that value in the array A2:C101 (not the originally intended A1:C100). On row 201, the vlookup in E201 will look at the value in D201 and search for it in the array A201:C300. If the values you want to search through are only in A1:C100, then your lookup won't find a match...ever. The $ "hard" references prevent this.

Finally, be mindful of the implications of the sort order variable in the function. If it is 1 or TRUE, the array must be sorted ascending, and the function returns the nearest value "below" or "before" the looked up value. If you want to force it to return only exact matches and/or don't want to or can't sort the array, be sure to set sort order to 0 or FALSE.

HTH,
Carl

#9

Thank you, both!

Carl Paulsen has written on 2/14/2014 5:06 PM:

#10

A few hints for vlookups: I find I usually want to add "hard"
references for the array so that it always looks at the intended rows of
the array. To do that, you need to add $ before each cell reference in
the array. So if the array of the function is A1:C100, I enter it as
$A$1:$C$100. This forces vlookup to always look at those cells
regardless of what row the calculation is happening on.

Another way of achieving that is to name the array. This has my
preference because a named array makes formulae easier to understand.

Finally, be mindful of the implications of the sort order variable in
the function. If it is 1 or TRUE, the array must be sorted ascending,
and the function returns the nearest value "below" or "before" the
looked up value. If you want to force it to return only exact matches
and/or don't want to or can't sort the array, be sure to set sort order
to 0 or FALSE.

+1 to all of these. The 0 (FALSE) parameter is the one I always use by
default for the reason you stated (getting an error when no match is found).

#11

Jean-Francois,

+1
Thanks for this idea, below. I'd never thought of naming an array, and you are right, it would seem to make both generating AND understanding formulae easier. Plus there's less room for error. In my case I frequently use the same array for a variety of vlookups, so the added step of naming could end up saving me some time as well.

Carl

#12

How do you name an array?

#13

(1) Select the array

(2) Menu Insert > Names > Define

(3) Enter the name of your choice (no spaces allowed) and hit OK.

From now on, using the array in a formula is equivalent to the absolute
reference to this array.

EG: you select A1:B2 and name it "my_array"
You may write formulae using the $A$1:$B$2 or the my_array syntax (note
that you must not use quotes when dealing with array names in formulae).
That is:
=SUM($A$1:$B$2)
or
=SUM(my_array)
give the same result

HTH,

#14

Jean-Francois Nifenecker has written on 2/15/2014 12:46 PM:

How do you name an array?

(1) Select the array

(2) Menu Insert > Names > Define

(3) Enter the name of your choice (no spaces allowed) and hit OK.

From now on, using the array in a formula is equivalent to the absolute
reference to this array.

EG: you select A1:B2 and name it "my_array"
You may write formulae using the $A$1:$B$2 or the my_array syntax (note
that you must not use quotes when dealing with array names in formulae).
That is:
=SUM($A$1:$B$2)
or
=SUM(my_array)
give the same result

HTH,

Merci, Jean-Francois!

What's the difference between this kind of named range and the one you
get by doing

Data > Define Range

in step 2?

#15

There, you define a dataset which can be further managed for data
retrieval, sorting, filtering, mailing...

#16

The column headers are seen as field names and the other rows as records.