"VLOOKUP??"

D

Don

Hi All,

Is it possible to pull data from another sheet using the
VLOOKUP function? I've looked at "Indirect", but that
seems to rely on typing in the name of another Sheet.

What I'd like to do is to establish a range of answers
(both text and numerical). This range would include
possibly 5-6 different responses to "Y" or "N" inputs. I
would like to put this range on a seperate sheet, let's
say Sheet2. Then on Sheet1 call a VLOOKUP dependent on
a "Y" or "N" in one cell, which would insert the
appropriate answer in another cell on Sheet1. This would
occur across Sheet1 in several locations and the
appropriate text/number would be pulled from Sheet2.

What I envision is a range set up something like this:

A1: Y B1: "text" C1: "text" D1: "value"
A2: N B2: "text" C2: "text" D2: "value"

Also might be entries in "E", "F" and so on, but if I get
this much sorted out, if it can be done, I can add to the
list.

Reason for doing this is that Sheet1 is to be sorted in
several different ways for different Invoice functions.
This would keep the LOOKUP range out of the sort.

I've managed to do this with nested "IF" functions so
far, but in some cases they become rather lengthy. If
there's an easier way, please let me know.

Hope this makes sense......be easy on me guys/gals...rank
beginner here.

Don.....dang, this got lengthy
 
B

Bob Phillips

Yes, just prefix the lookup range with the sheet name

Sheet1!A1:D10

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
D

Don

Thanks for the quick response Bob, but I must be doing
something wrong here. Below is what I inserted. By
adjusting the offset this calls up the various answers in
row 1 correctly, which is the Y response. But inserting
N in A1(sheet1) I get #NA. N is stored in A2 of the range
on sheet2...this should reflect the answers across row 2,
as I understand this......what am I missing here??

=VLOOKUP(A1,Sheet2!A1:D2,3)

Don
 
A

Alan Beban

The value in A2 of Sheet2 is probably not N; perhaps N preceded or
followed by one or more spaces?

Alan Beban
 
G

Guest

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me
correct answers across all four columns...Sorry, but
something is eluding me here.

I'll take a look at that site you gave Dave, thanks for
the input..

Don

-----Original Message-----
Hi Don,
You asked for the third column of the range and you only have
two columns in the range.

More information on VLOOKUP in
http://www.mvps.org/dmcritchie/excel/vlookup.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Don" <[email protected]> wrote in
message news:[email protected]...
 
G

Guest

Hi Alan,

Nope, Double checked what you said, even deleted contents
and reentered N in A2....still get the #N/A answer when N
is inserted in A1 of Sheet 1.....thanks for the advice
though...as indicated above in my response to Dave, I
think I'm misinterpreting something here......

Don
 
D

Doon

Thanks all for the info. It turns out I have to have
the "N" column first in A1...then the "Y" in
A2....ordered sequence....it's working fine with this
arrangement......:)

Thanks again all,

Don
 
D

David McRitchie

Sorry I got my rows and columns mixed up, I think I noticed
I was slipping yesterday when I indicated A2 when I meant B1.

To suppress N/A errors: with default False you are looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))
 
G

Guest

Hi Dave,

Not sure in your case, but when I do that, I can blame it
on old age.....lol

Anyway, as I stated below, I found the answer thanks to
the link you put up. N has to come before Y in the
List. It's working fine now...I inserted an If
(A1="","",VLOOKUP.......... Works great now...:)

Again, thanks for the help.

Don

-----Original Message-----
Sorry I got my rows and columns mixed up, I think I noticed
I was slipping yesterday when I indicated A2 when I meant B1.

To suppress N/A errors: with default False you are looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me



.
 
B

Bob Phillips

Don,

N doesn't have tgo come before Y, just add a final argument of FALSE

=VLOOKUP(A1,Sheet2!A1:D2,3,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Dave,

Not sure in your case, but when I do that, I can blame it
on old age.....lol

Anyway, as I stated below, I found the answer thanks to
the link you put up. N has to come before Y in the
List. It's working fine now...I inserted an If
(A1="","",VLOOKUP.......... Works great now...:)

Again, thanks for the help.

Don

-----Original Message-----
Sorry I got my rows and columns mixed up, I think I noticed
I was slipping yesterday when I indicated A2 when I meant B1.

To suppress N/A errors: with default False you are looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me



.
 
D

Don

Thanks again Bob,

Tried that in all combinations, works fine no matter what
the order.

A lesson I won't soon forget....:)

Don

-----Original Message-----
Don,

N doesn't have tgo come before Y, just add a final argument of FALSE

=VLOOKUP(A1,Sheet2!A1:D2,3,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Dave,

Not sure in your case, but when I do that, I can blame it
on old age.....lol

Anyway, as I stated below, I found the answer thanks to
the link you put up. N has to come before Y in the
List. It's working fine now...I inserted an If
(A1="","",VLOOKUP.......... Works great now...:)

Again, thanks for the help.

Don

-----Original Message-----
Sorry I got my rows and columns mixed up, I think I noticed
I was slipping yesterday when I indicated A2 when I meant B1.

To suppress N/A errors: with default False you are looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not found",VLOOKUP(...,...,...,False))
changed
Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give me



.


.
 
B

Bob Phillips

It should be the default :)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Don said:
Thanks again Bob,

Tried that in all combinations, works fine no matter what
the order.

A lesson I won't soon forget....:)

Don

-----Original Message-----
Don,

N doesn't have tgo come before Y, just add a final argument of FALSE

=VLOOKUP(A1,Sheet2!A1:D2,3,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Hi Dave,

Not sure in your case, but when I do that, I can blame it
on old age.....lol

Anyway, as I stated below, I found the answer thanks to
the link you put up. N has to come before Y in the
List. It's working fine now...I inserted an If
(A1="","",VLOOKUP.......... Works great now...:)

Again, thanks for the help.

Don


-----Original Message-----
Sorry I got my rows and columns mixed up, I think I
noticed
I was slipping yesterday when I indicated A2 when I
meant B1.

To suppress N/A errors: with default False you are
looking for
an exact match.

=IF(ISNA(VLOOKUP(...,...,...,False)),"Item not
found",VLOOKUP(...,...,...,False))

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed
Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm

hmmmmmmm.....I must be misinterpreting something
here....as I look at what I have, I have 4 columns....A
thru D, and two Rows 1 & 2. Why does the Y give 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