I need some help with a worksheet Function (Lookup?)

[BigG]

Hi, I have a question. I am trying to put together a worksheet with
few different functions and I need some help because I can’t seem t
figure it out. I will try to describe the best I can what I am tryin
to do.
Okay I have a list of material (example, ¼-20, ½-20, 1-30, M-130
M-114) there are about 18 different materials altogether. Okay each o
these represent a different SIZE, for example ¼-20 = .2458, ½-20
.4520, M-130 = .330
So each material has its own size. Now I have the materials listed in
column on a second page of the spreadsheet with the sizes listed in a
adjacent column with the rows lining up matching the material to th
size. Example column C has all the materials listed from C1 to C18 an
next to it I have D1 to D18 listing the SIZES matching up from left t
right the material with the sizes.

Okay, what I need is when I type in a MATERIAL in cell E4 I need it t
place the matching SIZE in cell B8. I am trying to use LOOKUP for thi
but I can’t seem to figure it out.
So if anyone can help me out that would be great


Thanks

 

[Paul]

Hi, I have a question. I am trying to put together a worksheet with a
few different functions and I need some help because I can't seem to
figure it out. I will try to describe the best I can what I am trying
to do.
Okay I have a list of material (example, ¼-20, ½-20, 1-30, M-130,
M-114) there are about 18 different materials altogether. Okay each of
these represent a different SIZE, for example ¼-20 = .2458, ½-20 =
4520, M-130 = .330
So each material has its own size. Now I have the materials listed in a
column on a second page of the spreadsheet with the sizes listed in an
adjacent column with the rows lining up matching the material to the
size. Example column C has all the materials listed from C1 to C18 and
next to it I have D1 to D18 listing the SIZES matching up from left to
right the material with the sizes.

Okay, what I need is when I type in a MATERIAL in cell E4 I need it to
place the matching SIZE in cell B8. I am trying to use LOOKUP for this
but I can't seem to figure it out.
So if anyone can help me out that would be great

In cell B8 you need this formula:
=VLOOKUP(E4,Sheet2!C118,2,0)
(Replace Sheet2 with the name of your second worksheet.)

 

[Roger Govier]

Hi BigG

Try entering in cell B8
=VLOOKUP(E4,'Sheet2'!$B$1:$B$18,2,0)

 

[Roger Govier]

Sorry that was meant to read
=VLOOKUP(E4,'Sheet2'!$C$1:$D$18,2,0)



--
Regards
Roger Govier

Hi BigG

Try entering in cell B8
=VLOOKUP(E4,'Sheet2'!$B$1:$B$18,2,0)

 

[Bernard V Liengme]

Hi,
The trouble is the simple LOOKUP expects things in order. Use either VLOOKUP
or HLOOKUP where you can specify FALSE as the third argument to indicate you
want an exact match and that the array is not ordered. Come back if you need
more details.
Bernard

 

[Bob Phillips]

G,

In B8 use

=INDEX(Sheet2!$C$12:$C$18,MATCH(E4,Sheet2!$D$1:$D$18,0),1)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[BigG]

You guys are great! Works like a charm!
The one I used was =VLOOKUP(E4,'Sheet2'!$C$1:$D$18,2,0)

Now the question I have is, if anyone feels like explaining it is tha
I tryed this myself in Excel using the VLOOKUP function and I'l
explain what I did and what the result was.

First off I used the insert fuction off of the top menu. Then I chos
VLOOKUP, in the first box(value) I clicked on the E4 square where i
was to look for the value typed in.

Then the next box (table array) I clicked onto sheet 2 and highlighte
the table I made, I clicked on C1, held the mouse down, dragged an
ended at D22*note the number d18 I gave earlier was wrong actuall
there were 22 rows

The third Box (Col Index_number) I clicked on the B8 square where
wanted my result.

The last box (Range Lookup)I left blank, I did not know what to do wit
it.
This was the formula it gave me
=VLOOKUP(E4,Sheet2!C122,B8)
Which obviously did not work, it said It was a circular reference.
Just trying to figure out what I did wrong, I see your guys formula
have $$ in them and mine does not for one thing. And the end i
different.

You all have helped me out a great deal already and it's working fin
with what you gave me, I'm just one of those people I guess who have t
know WHY, HOW and WHAT did I did wrong (It's a hangup of mine sometime
)

Thanks again to all!!!!!!

 

[Paul]

You guys are great! Works like a charm!
The one I used was =VLOOKUP(E4,'Sheet2'!$C$1:$D$18,2,0)

Now the question I have is, if anyone feels like explaining it is that
I tryed this myself in Excel using the VLOOKUP function and I'll
explain what I did and what the result was.

First off I used the insert fuction off of the top menu. Then I chose
VLOOKUP, in the first box(value) I clicked on the E4 square where it
was to look for the value typed in.

Then the next box (table array) I clicked onto sheet 2 and highlighted
the table I made, I clicked on C1, held the mouse down, dragged and
ended at D22*note the number d18 I gave earlier was wrong actually
there were 22 rows

The third Box (Col Index_number) I clicked on the B8 square where I
wanted my result.

The last box (Range Lookup)I left blank, I did not know what to do with
it.
This was the formula it gave me
=VLOOKUP(E4,Sheet2!C122,B8)
Which obviously did not work, it said It was a circular reference.
Just trying to figure out what I did wrong, I see your guys formulas
have $$ in them and mine does not for one thing. And the end is
different.

You all have helped me out a great deal already and it's working fine
with what you gave me, I'm just one of those people I guess who have to
know WHY, HOW and WHAT did I did wrong (It's a hangup of mine sometimes
)

You have to be in the cell where you want the result when you start entering
the formula, whether you are going to type it in or use the wizard. What you
did was fine until you got to the third box. There you should have entered 2
as you wanted the result returned from the second column of the range you
had just entered. For an exact match (which doesn't require the data in the
lookup range to be in sorted order), you should also have entered 0 (or
FALSE) in the fourth box.

The wizard gives a brief description of what is needed in each box as you
click in them. For a full description of the function and each of its
parameters, and often some examples, use the Index tab in Help and type in
the function name.

 

[Bob Phillips]

The last box (Range Lookup)I left blank, I did not know what to do with
it.
This was the formula it gave me
=VLOOKUP(E4,Sheet2!C122,B8)
Which obviously did not work, it said It was a circular reference.

It's a circular reference because you are putting the formula in B8, but
also referencing B8 in the formula, a no no!. The 3rd argument for VLOOKUP
is the column that you want to pull the value from, relative to the table
(1,2,3 etc.). The 4tgh argument is set to False if your lookup table is not
in order.

Just trying to figure out what I did wrong, I see your guys formulas
have $$ in them and mine does not for one thing. And the end is
different.

This is done so that if you copy the formula down to other cells, it does
not shioft the lookup table along. For instance, your formula if copied to
B9 would update to =VLOOKUP(E4,Sheet2!C223,B8) which sooner or later will
create a problem. By using $ signs, you fix the cell references.

 

[BigG]

Just trying to figure out what I did wrong, I see your guys formulas
have $$ in them and mine does not for one thing. And the end is
different.

This is done so that if you copy the formula down to other cells, it
does
not shioft the lookup table along. For instance, your formula if
copied to
B9 would update to =VLOOKUP(E4,Sheet2!C223,B8) which sooner or
later will
create a problem. By using $ signs, you fix the cell references.

[

So if I am getting this is that if you copy the formula down 5 spaces
it will change your reference down 5 spaces also?

So is this dollar sign thing something that is just done manualy then
or is there an option durring the function wizard or somewhere else to
do this?


G.

 

[Bob Phillips]

Yes, you are correct.

Adding the $ is done manually, but you can select the cell in the formula
and F4 to cycle through all of the absolute/relative options.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Just trying to figure out what I did wrong, I see your guys formulas
have $$ in them and mine does not for one thing. And the end is
different.

This is done so that if you copy the formula down to other cells, it
does
not shioft the lookup table along. For instance, your formula if
copied to
B9 would update to =VLOOKUP(E4,Sheet2!C223,B8) which sooner or
later will
create a problem. By using $ signs, you fix the cell references.

[

So if I am getting this is that if you copy the formula down 5 spaces
it will change your reference down 5 spaces also?

So is this dollar sign thing something that is just done manualy then
or is there an option durring the function wizard or somewhere else to
do this?


G.