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???????
 
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???????
Click to expand...
Click to expand...
 
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???????
Click to expand...
Click to expand...
Click to expand...
 
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???????
Click to expand...


.
Click to expand...
 
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???????
Click to expand...
Click to expand...
Click to expand...
 
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???????
Click to expand...


.
Click to expand...
 
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???????
Click to expand...
Click to expand...
Click to expand...
 
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???????
Click to expand...


.
Click to expand...
 
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
Click to expand...
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???????
Click to expand...
Click to expand...

- Show quoted text -
Click to expand...
 
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???????
Click to expand...
Click to expand...
Click to expand...
 

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

Similar Threads

Excel - Wich Function should I use? 1
Copy and shift data automaticaly 0
Vlookup Returning Zero For Every Cell 3
Cumulative formula for word table 0
Use & with two formulas 2
Problem with SET & REF function 0
Vlookup and return sheet name also 2
Help With VLOOKUP 2

Top