Problem with Functions and Formulas

[Kris]

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

 

[Bob Phillips]

It may be that the data is not sorted, so try

=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,FALSE))

 

[Jacob Skaria]

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes

 

[Kris]

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes
---------------
Jacob Skaria

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

 

[Jacob Skaria]

Oops...corrected

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2,0))

If this post helps click Yes
---------------
Jacob Skaria

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes
---------------
Jacob Skaria

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

 

[Kris]

Thanks so much for your help, but it still didn't work.

-
Kris "New Excel User"

It may be that the data is not sorted, so try

=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,FALSE))

--
__________________________________
HTH

Bob

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the
#s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula,
or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

.

 

[Kris]

Ooh Jacob that didn't work either. This thing is due today by 1pm MST. I
think I just might go crazy. LOL
Keepum coming
--
Kris "New Excel User"

Oops...corrected

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2,0))

If this post helps click Yes
---------------
Jacob Skaria

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes
---------------
Jacob Skaria


:

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

 

[Kris]

Bob, what do you mean "the data is not sorted"?
--
Kris "New Excel User"

It may be that the data is not sorted, so try

=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,FALSE))

--
__________________________________
HTH

Bob

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the
#s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula,
or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

.

 

[Kris]

Come on Jacob. Did you give up on me? I had to take off for a bit and get
some blood drawn. Buy I'm back now! Bob said something about the data isn't
"sorted". Is that something I need to do first?
thanks
--
Kris "New Excel User"

Oops...corrected

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2,0))

If this post helps click Yes
---------------
Jacob Skaria

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes
---------------
Jacob Skaria


:

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

 

[Kris]

Okay Bab. I had to take off for a bit and get some lab work done, but I'm
back now. What was that about "data not sorted"? Could that help and what do
I do?
Thanks
--
Kris "New Excel User"

It may be that the data is not sorted, so try

=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,FALSE))

--
__________________________________
HTH

Bob

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the
#s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula,
or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

.

 

[Pete_UK]

I don't know when 1pm MST is compared to BST, but Jacob's formula
should have worked based on the information you have given. Perhaps
your IDs in one sheet are text values that just look like numbers and
the IDs in the other sheet are proper numbers, and an exact match
cannot be found.

It doesn't help us to try to solve the problem if you say "that didn't
work". In what way did it not work? Did you get some error message in
the cell where the formula is, like #N/A? Did you get some value that
you were not expecting? Did you get a blank cell ???

Please describe what you did get and we might be able to offer further
advice before your deadline.

Pete

Come on Jacob. Did you give up on me? I had to take off for a bit and get
some blood drawn. Buy I'm back now! Bob said something about the data isn't
"sorted". Is that something I need to do first?
thanks
--
Kris "New Excel User"

Oops...corrected

If this post helps click Yes

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function.." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"
:
Hi Kris
With id in Sheet2 G3 try the below formula in cell sheet H3...whichwill
return the corresponding name from Sheet1.
=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))
If this post helps click Yes
---------------
Jacob Skaria
:
Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????

- Show quoted text -

 

[Jacob Skaria]

Try this test...

In Sheet1
ColA ColB
Name Age
Adam 32
Marie 24
Francis 33

In Sheet2

A1 = Adam
In B1 enter formula
=VLOOKUP(A1,Sheet1!A:B,2,0)

'To handle blanks
=IF(A1="","",VLOOKUP(A1,Sheet1!A:B,2,0))

If this post helps click Yes
---------------
Jacob Skaria

Come on Jacob. Did you give up on me? I had to take off for a bit and get
some blood drawn. Buy I'm back now! Bob said something about the data isn't
"sorted". Is that something I need to do first?
thanks
--
Kris "New Excel User"

Oops...corrected

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2,0))

If this post helps click Yes
---------------
Jacob Skaria

Thank you so much Jacob for answering. I entered the formula in H3 and I got
an error message,"A value is not available to the formula or function." When
I entered client 1 in G3, #N/A is what I got in H3??????? Any ideas for me?
Thanks
Kris
--
Kris "New Excel User"


:

Hi Kris

With id in Sheet2 G3 try the below formula in cell sheet H3...which will
return the corresponding name from Sheet1.

=IF(G3="","",VLOOKUP(G3,Sheet1!A:B,2))

If this post helps click Yes
---------------
Jacob Skaria


:

Question: I am working in Excel and I have 2 worksheets. Sheet 1 is the
client information with an ID# assigned to each client. Column A has the #s
starting with row 2, name in B2.
Sheet 2 is my sales sheet. I want to be able, and I know this is a little
backwards, to enter the ID# in Column G (starting at line 3), to get the
Client name (from sheet 1) to appear in Column H automatically.
I cannot figure out where to enter the formula, how to write the formula, or
what function to use.
I have been told to use the VLOOKUP and add the IF function.
"=IF(A1="","",VLOOKUP(Sheet2!A1,Sheet1!A:B,2))", this came from my
professor. Oh by the way I just finished Bis155 (Data Analysis
w/Spreadsheet,Excel)
I have also been told to enter this - =VLOOKUP(Table2[[#This Row],[Phone
Number]],Table3[[Column1]:[ID'#]],2,FALSE) . But he had it all wrong.
Can someone please help me???????