Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:

=IF(isna(LOOKUP(E138,'Pipe Specs'!B:B,false)),"Not Found",LOOKUP(E138,'Pipe
Specs'!B:B,'Pipe Specs'!C:C))

"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

hey there buddy.. I tried your formula and it returns a "#N/A"

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:

=IF(isna(LOOKUP(E138,'Pipe Specs'!B:B,false)),"Not Found",LOOKUP(E138,'Pipe
Specs'!B:B,'Pipe Specs'!C:C))

"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:

=IF(isna(LOOKUP(E138,'Pipe Specs'!B:B,false)),"Not Found",LOOKUP(E138,'Pipe
Specs'!B:B,'Pipe Specs'!C:C))

"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:

=IF(isna(LOOKUP(E138,'Pipe Specs'!B:B,false)),"Not Found",LOOKUP(E138,'Pipe
Specs'!B:B,'Pipe Specs'!C:C))

"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

hello again,

I tried putting in VLOOKUP instead of LOOKUP. It still returns #value into
the cell. Any suggestions or ideas? Hmm it must be something simple.
Thanks again for the prompt reply.

"Jim Thomlinson" wrote:

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:

=IF(isna(LOOKUP(E138,'Pipe Specs'!B:B,false)),"Not Found",LOOKUP(E138,'Pipe
Specs'!B:B,'Pipe Specs'!C:C))

"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

So let me see if I have this straight. You want to look up Spec based on the
name...

Try =IF(isna(vLOOKUP(E138,'Pipe Specs'!B:E,1,false)),"Not
Found",vLOOKUP(E138,'Pipe Specs'!B:E, 2))

The 2 of the offset to the right of the B column (so return the valuye in
column C)
"LoboNetwork" wrote:

hello again,

I tried putting in VLOOKUP instead of LOOKUP. It still returns #value into
the cell. Any suggestions or ideas? Hmm it must be something simple.
Thanks again for the prompt reply.

"Jim Thomlinson" wrote:

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:


"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

hey Jim,

I've used the updated code. It only seems to work when the items are
sorted. If i add anything to the bottom of the list, it gets a different "C"
column number. I need it to work without having to resort somehow... hmm
i'll try play around with it. Any help would be greatly appreciated though.

"Jim Thomlinson" wrote:

So let me see if I have this straight. You want to look up Spec based on the
name...

Try =IF(isna(vLOOKUP(E138,'Pipe Specs'!B:E,1,false)),"Not
Found",vLOOKUP(E138,'Pipe Specs'!B:E, 2))

The 2 of the offset to the right of the B column (so return the valuye in
column C)
"LoboNetwork" wrote:

hello again,

I tried putting in VLOOKUP instead of LOOKUP. It still returns #value into
the cell. Any suggestions or ideas? Hmm it must be something simple.
Thanks again for the prompt reply.

"Jim Thomlinson" wrote:

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:


"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

actually on further inspection, it doesnt seem to say "Not Found" at all
now.... with the new formula... it just seems to get the same value of 102.3

*confused*

"Jim Thomlinson" wrote:

So let me see if I have this straight. You want to look up Spec based on the
name...

Try =IF(isna(vLOOKUP(E138,'Pipe Specs'!B:E,1,false)),"Not
Found",vLOOKUP(E138,'Pipe Specs'!B:E, 2))

The 2 of the offset to the right of the B column (so return the valuye in
column C)
"LoboNetwork" wrote:

hello again,

I tried putting in VLOOKUP instead of LOOKUP. It still returns #value into
the cell. Any suggestions or ideas? Hmm it must be something simple.
Thanks again for the prompt reply.

"Jim Thomlinson" wrote:

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:


"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.

Yesssss!!

I have found the solution after playing around with it!

=IF(ISNA(VLOOKUP(E159,'Pipe Specs'!B:E,1,0)),"Not Found",VLOOKUP(E159,'Pipe
Specs'!B:E, 2,0))

Hope that helps anyone with similar problems.

Thank you Jim for all your help.

"Jim Thomlinson" wrote:

So let me see if I have this straight. You want to look up Spec based on the
name...

Try =IF(isna(vLOOKUP(E138,'Pipe Specs'!B:E,1,false)),"Not
Found",vLOOKUP(E138,'Pipe Specs'!B:E, 2))

The 2 of the offset to the right of the B column (so return the valuye in
column C)
"LoboNetwork" wrote:

hello again,

I tried putting in VLOOKUP instead of LOOKUP. It still returns #value into
the cell. Any suggestions or ideas? Hmm it must be something simple.
Thanks again for the prompt reply.

"Jim Thomlinson" wrote:

Sorr should be Vlookup not lookup in the formula

"LoboNetwork" wrote:

ah i got it to say #VALUE now
but its still not the right value... hehe

The #n/a was occuring because i had a <return in my formula...hmmm
I need a solution that works without having to sort... there are many of
these documents and tons of pipe specs.

Thanks for all your help above :)

"Jim Thomlinson" wrote:

When you add the new specs do you re-sort the sheet? If not try resorting the
sheet and see if it makes a difference.

The VLookup function has a third optional argument

=Vlookup(What, In Where, How) The How part is a True or False. If you do not
specify the How argument then you must make sure that your source data is
sorted or you may not find what you are looking for. Not specifying is the
same as adding True which finds the closest match, not an exact match. If you
specify False then only exact matches are returned.

That being said you could change your formula to:


"LoboNetwork" wrote:

Hello,

I have a spreadsheet with two different sheets.
Sheet 2 has Pipe Specs..

ie. sheet 2
column 2 has "names"
column 3 has "inches"
name1 32.1
name2 64.1
etc

sheet 1 has a lot more data. It has the pipe spec names as well... and
there is a formula that I created to grab data from sheet2 and put it in the
adjacent cell in sheet1.

so in sheet 1.. i say i have "name1"... the cell beside it contains a
formula that will automatically put in 32.1.

The formula i used is-

=IF(LOOKUP(E138,'Pipe Specs'!B:B)=E138,LOOKUP(E138,'Pipe Specs'!B:B,'Pipe
Specs'!C:C),"Not Found")

the problem i have is.. that when i add a new pipe spec in sheet2... it
comes up as "Not Found" on sheet1.... but all the older ones get the right
inches..

any help would be greatly appreciated. I think i am missing somethign simple.