Lookup between two worksheets
 

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Thanks Mike, you are a star
"Mike H" wrote:

Make the reference absolute with the $ sign and it won't increment

$A$1:$C$3

"whatzzup" wrote:

Hi Mike

As I said thanks for that. I am intending to drag this formulae down in the
worksheet but I need to constrain the Sheet1!A1:C3 part so that it doesn't
increment with the A30 part. is this possible?????

"Mike H" wrote:

Typo correction

A1:C3 is the array to find it in( i.e A1 - A3, B1-B3, C1-C3) on Sheet 1

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Mike

Sorry, I did try it but must have type it wrong or something. You've saved
me loads of time.

Cheers, will have to come on here more often when I have problems.

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Typo correction

A1:C3 is the array to find it in( i.e A1 - A3, B1-B3, C1-C3) on Sheet 1

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Make the reference absolute with the $ sign and it won't increment

$A$1:$C$3

"whatzzup" wrote:

Hi Mike

As I said thanks for that. I am intending to drag this formulae down in the
worksheet but I need to constrain the Sheet1!A1:C3 part so that it doesn't
increment with the A30 part. is this possible?????

"Mike H" wrote:

Typo correction

A1:C3 is the array to find it in( i.e A1 - A3, B1-B3, C1-C3) on Sheet 1

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Hi Mike

As I said thanks for that. I am intending to drag this formulae down in the
worksheet but I need to constrain the Sheet1!A1:C3 part so that it doesn't
increment with the A30 part. is this possible?????

"Mike H" wrote:

Typo correction

A1:C3 is the array to find it in( i.e A1 - A3, B1-B3, C1-C3) on Sheet 1

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Remember that while using the vlookup function, the data in sheet1 should be
in sorted on column A in ascending order....



"whatzzup" wrote:

Mike

Sorry, I did try it but must have type it wrong or something. You've saved
me loads of time.

Cheers, will have to come on here more often when I have problems.

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1

Lookup between two worksheets
 

Not if the FALSE argument is used as OP shows.

From Help..............

If range_lookup is TRUE, the values in the first column of table_array must be
placed in ascending order: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE;
otherwise VLOOKUP may not give the correct value. If range_lookup is FALSE,
table_array does not need to be sorted.


Gord Dibben MS Excel MVP

On Sat, 18 Aug 2007 08:48:01 -0700, Sameer Nagi
wrote:

Remember that while using the vlookup function, the data in sheet1 should be
in sorted on column A in ascending order....



"whatzzup" wrote:

Mike

Sorry, I did try it but must have type it wrong or something. You've saved
me loads of time.

Cheers, will have to come on here more often when I have problems.

"Mike H" wrote:

Which is precisely what the formula does. An explanation of Vlookup
=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

A30 is the value to find
A1:30 is the array to find it in( i.e A1 - A3, B1-B3, B1-B3) on Sheet 1
vlookup looks in the leftmost column in the case Column A
3 is the coulmn of the array to return the value from
False indicates exact matches only.

Did you actually try it?

Mike
"whatzzup" wrote:

Thanks Mike

What I should have said is that the value in A30 (sheet 2) could be anywhere
in column A (sheet 1) so therefore the formulae needs to find the value in
A30 somewhere in column A (sheet 1) and then the corresponding value in that
C colunm.

If you can help that would be great

"Mike H" wrote:

Try this in C30

=VLOOKUP(A30,Sheet1!A1:C3,3,FALSE)

Mike

"whatzzup" wrote:

I have the foll prob, i have two worhsheets with values in column A matching
values in column C A1 = 6 and C1 = Cat
A B C
1 6 Dog Cat
2 7 Pig Rat
3 17 x y
Next sheet A30 = 6 and C30 = ?
A B C
30 6 fly ?
31 3 tin can
32 17 d n
Can i write a formula in second worksheet to go look in first worksheet and
see the relationship between A1/C1 and get the value of C1 and make C32 = C1