Search and Get Function Help

[angelsimpson]

Hi. I'm trying to compare columns from one sheet to another, and if there is a match, pull data in a column next to it. I tried to use the DGET, but I couldn't get it to work properly. See example below:

Worksheet 1
2
4
1
2
5
4
3

Worksheet 2
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5

What I'm looking to do is have Worksheet 1 show the description next to the number, see below:
2 Test 2
4 Test 4
1 Test 1
2 Test 2
5 Test 5
4 Test 4
3 Test 3


I know that this can easily be done with sql, but I wasn't sure if I could do it that easily in excel. Thanks in advance for your help.

Angel

[Domenic]

On Worksheet 1...

B1, copied down:

=VLOOKUP(A1,'Worksheet 2'!$A$1:$B$5,2,0)

Change the reference for 'Worksheet 2' to your actual sheet name.

Hope this helps!

Hi. I'm trying to compare columns from one sheet to another, and if there is a match, pull data in a column next to it. I tried to use the DGET, but I couldn't get it to work properly. See example below:

Worksheet 1
2
4
1
2
5
4
3

Worksheet 2
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5

What I'm looking to do is have Worksheet 1 show the description next to the number, see below:
2 Test 2
4 Test 4
1 Test 1
2 Test 2
5 Test 5
4 Test 4
3 Test 3


I know that this can easily be done with sql, but I wasn't sure if I could do it that easily in excel. Thanks in advance for your help.

Angel

[swatsp0p]

Use VLOOKUP on sheet 1. Let's assume your data is in Cols A:B on sheet2 and Col A on sheet1, both beginning in row 1 through row 25.

On sheet1, in B1 place this formula:

=VLOOKUP(A1,Sheet2!$A$1:$A$25,2,0)

then copy this formula down through B25

Good Luck

[angelsimpson]

That was a huge help... however several of my rows are saying #N/A instead of the actual text that I was expecting, do you know what might be causing this?

Thanks!
Angel

[angelsimpson]

I just realized what is going on... when I copied the formula down my column, it actually changed the formula as it went down... for example, see a row of what my formulas are looking like... is there a way to change this?

=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
=VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
=VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
=VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
=VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)

So by the time it gets to the bottom of my list, the "lookup" its looking at is not even valid information anymore.

Angel

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[swatsp0p]

Hmmmm, this is strange. Your Fixed data range ('Worksheet 2'!$A$1:$B$5) shouldn't change as you copy down formulas. The only reference that should change as you go down the column is A1, A2, A3... for the lookup value. The table reference should remain unchanged.

I actually prefer to NAME my table range and use that name in my formula. Try naming your range on Sheet2. Highlight the data range that you want to use, then click Insert on the main menu, select Name then Define. Enter a name for this range (Table1) and click OK.

Now, change your formula to read:

=VLOOKUP(A1,Table1,2,0)

copy this formula down column B as far as you need for the list in column A.

HTH

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>

[Ron Coderre]

See if this works:

On sheet1 enter this formula in B2 and copy down:
=vlookup(A2,Sheet2!A2:B10,2,0)

You'll need to change the references to suit your situation.

Does that help?
--
Regards,
Ron

[Duke Carey]

use VLOOKUP() instead



"angelsimpson" wrote:

>
> Hi. I'm trying to compare columns from one sheet to another, and if
> there is a match, pull data in a column next to it. I tried to use the
> DGET, but I couldn't get it to work properly. See example below:
>
> Worksheet 1
> 2
> 4
> 1
> 2
> 5
> 4
> 3
>
> Worksheet 2
> 1 Test 1
> 2 Test 2
> 3 Test 3
> 4 Test 4
> 5 Test 5
>
> What I'm looking to do is have Worksheet 1 show the description next to
> the number, see below:
> 2 Test 2
> 4 Test 4
> 1 Test 1
> 2 Test 2
> 5 Test 5
> 4 Test 4
> 3 Test 3
>
>
> I know that this can easily be done with sql, but I wasn't sure if I
> could do it that easily in excel. Thanks in advance for your help.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile: http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>
>

[Daniel CHEN]

If your formula like
=VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
then
A1 will automatically changes because it uses relative address - this is
want you want!
Sheet1!$A$1:$A$26 should not change when you copy/paste to another place
because it uses absolute address.
! You need have four "$"s to get absolute address. Sheet1!A1:A26 is a
relative address and will auto changes. Sheet1!$A$1:$A$26 is an absolute
address.

===== * ===== * ===== * =====
Daniel CHEN

Spreadsheet/VBA Specialist
[email protected]
www.Geocities.com/UDQServices
Your "Impossible" Task Could Be Someone Else's "Piece of Cake"
===== * ===== * ===== * =====




"angelsimpson" <[email protected]>
wrote in message
news:[email protected]...
>
> I just realized what is going on... when I copied the formula down my
> column, it actually changed the formula as it went down... for example,
> see a row of what my formulas are looking like... is there a way to
> change this?
>
> =VLOOKUP(A1,Sheet1!$A$1:$A$26,2,0)
> =VLOOKUP(A2,Sheet1!$A$2:$A$27,2,0)
> =VLOOKUP(A3,Sheet1!$A$3:$A$28,2,0)
> =VLOOKUP(A4,Sheet1!$A$4:$A$29,2,0)
> =VLOOKUP(A5,Sheet1!$A$5:$A$30,2,0)
>
> So by the time it gets to the bottom of my list, the "lookup" its
> looking at is not even valid information anymore.
>
> Angel
>
>
> --
> angelsimpson
> ------------------------------------------------------------------------
> angelsimpson's Profile:
> http://www.excelforum.com/member.php...o&userid=23931
> View this thread: http://www.excelforum.com/showthread...hreadid=375625
>