Problem with Functions and Formulas


K

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???????
 
Ad

Advertisements

B

Bob Phillips

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

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

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
 
K

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"


Jacob Skaria said:
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


Kris said:
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???????
 
J

Jacob Skaria

Oops...corrected

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

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


Kris said:
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"


Jacob Skaria said:
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


Kris said:
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???????
 
K

Kris

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

-
Kris "New Excel User"


Bob Phillips said:
It may be that the data is not sorted, so try

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

--
__________________________________
HTH

Bob

Kris said:
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???????


.
 
Ad

Advertisements

K

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"


Jacob Skaria said:
Oops...corrected

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

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


Kris said:
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"


Jacob Skaria said:
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???????
 
K

Kris

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


Bob Phillips said:
It may be that the data is not sorted, so try

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

--
__________________________________
HTH

Bob

Kris said:
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???????


.
 
K

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"


Jacob Skaria said:
Oops...corrected

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

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


Kris said:
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"


Jacob Skaria said:
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???????
 
K

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"


Bob Phillips said:
It may be that the data is not sorted, so try

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

--
__________________________________
HTH

Bob

Kris said:
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???????


.
 
P

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"



Jacob Skaria said:
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 -
 
Ad

Advertisements

J

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


Kris said:
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"


Jacob Skaria said:
Oops...corrected

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

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


Kris said:
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???????
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top