"2-Way" Lookup?
 

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

Try something like this:

With
The extracted data in Capture!A1:D100
The display column headings in Display!B1:D1
The display row headings in Display!A2:A50

Then, on the Display sheet

B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)
Copy that formula down and right to Display!D50

The formula uses a standard VLOOKUP, but uses the MATCH function to return
which column to use.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

Assuming the data on capture is in A1:M20, the heading on Display are B1:M1,
sub-headings are A2:A20, then in B2

=INDEX(Capture!$B$2:$M$20,MATCH(Display!$A2,Displa y!$A$2:$A$20,0),MATCH(Disp
lay!B$1,Capture!$B$1:$M$1,0))

copy down and across.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

I'd read the notes that Debra Dalgleish shares:
http://www.contextures.com/xlFunctions03.html

Example 2 might be on point.

wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

--

Dave Peterson

"2-Way" Lookup?
 

Hi all!

I found Ron's solution to be the easiest for me. It works like a charm
and I couldn't be happier. Question though - what is the last zero in
the formula for? Dave's link helped explain the second to last one.

Bob - your solution would have worked I think but honestly I had
trouble sorting out the pieces. I'm going to keep a copy of it on hand
though, because you never know where it might come in useful.

And Dave - I looked up your link and it helped translate the "0" in
Ron's answer.

Thank you all for your time and efforts in helping. This will save me
a great deal of headache going forward.

Mrs. Rum



Ron Coderre wrote:
Try something like this:

With
The extracted data in Capture!A1:D100
The display column headings in Display!B1:D1
The display row headings in Display!A2:A50

Then, on the Display sheet

B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)
Copy that formula down and right to Display!D50

The formula uses a standard VLOOKUP, but uses the MATCH function to return
which column to use.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

Thanks for the feedback.....I'm glad that worked for you.


I'm not sure if you still had a question about the last zero in the formula
I posted:
B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)

That zero is the 4th argument in the VLOOKUP function and indicates that an
EXACT MATCH is required. (A one would indicate an approximate match)

Simplified example:

With this table in A1:B5....
1 a
2 b
3 c
4 d
2.5 e

C1: =VLOOKUP(2.5,A1:B5,2,0)
That formula (with an exact match) returns "e"

BUT
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an exact match) returns "b"!
An approximate match assumes that the data list is sorted, so in that
instance, it searches until it finds a value larger than the search
value...and returns the previous value. 3 is larger than 2.5, so "b" was
returned....even though 2.5 is further down the list.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi all!

I found Ron's solution to be the easiest for me. It works like a charm
and I couldn't be happier. Question though - what is the last zero in
the formula for? Dave's link helped explain the second to last one.

Bob - your solution would have worked I think but honestly I had
trouble sorting out the pieces. I'm going to keep a copy of it on hand
though, because you never know where it might come in useful.

And Dave - I looked up your link and it helped translate the "0" in
Ron's answer.

Thank you all for your time and efforts in helping. This will save me
a great deal of headache going forward.

Mrs. Rum



Ron Coderre wrote:
Try something like this:

With
The extracted data in Capture!A1:D100
The display column headings in Display!B1:D1
The display row headings in Display!A2:A50

Then, on the Display sheet

B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)
Copy that formula down and right to Display!D50

The formula uses a standard VLOOKUP, but uses the MATCH function to return
which column to use.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

typo correction:

This:
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an exact match) returns "b"!

Should be:
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an APPROXIMATE match) returns "b"!

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Thanks for the feedback.....I'm glad that worked for you.


I'm not sure if you still had a question about the last zero in the formula
I posted:
B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)

That zero is the 4th argument in the VLOOKUP function and indicates that an
EXACT MATCH is required. (A one would indicate an approximate match)

Simplified example:

With this table in A1:B5....
1 a
2 b
3 c
4 d
2.5 e

C1: =VLOOKUP(2.5,A1:B5,2,0)
That formula (with an exact match) returns "e"

BUT
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an exact match) returns "b"!
An approximate match assumes that the data list is sorted, so in that
instance, it searches until it finds a value larger than the search
value...and returns the previous value. 3 is larger than 2.5, so "b" was
returned....even though 2.5 is further down the list.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi all!

I found Ron's solution to be the easiest for me. It works like a charm
and I couldn't be happier. Question though - what is the last zero in
the formula for? Dave's link helped explain the second to last one.

Bob - your solution would have worked I think but honestly I had
trouble sorting out the pieces. I'm going to keep a copy of it on hand
though, because you never know where it might come in useful.

And Dave - I looked up your link and it helped translate the "0" in
Ron's answer.

Thank you all for your time and efforts in helping. This will save me
a great deal of headache going forward.

Mrs. Rum



Ron Coderre wrote:
Try something like this:

With
The extracted data in Capture!A1:D100
The display column headings in Display!B1:D1
The display row headings in Display!A2:A50

Then, on the Display sheet

B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)
Copy that formula down and right to Display!D50

The formula uses a standard VLOOKUP, but uses the MATCH function to return
which column to use.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum

"2-Way" Lookup?
 

Ah! Exact match. It makes sense if I think of it in terms of the
VLookup part and not the Match part.

Thanks again for your help! I've completed all the changes in my
workbook and it's exactly what I needed.

Mrs. Rum

Ron Coderre wrote:
typo correction:

This:
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an exact match) returns "b"!

Should be:
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an APPROXIMATE match) returns "b"!

***********
Regards,
Ron

XL2002, WinXP


"Ron Coderre" wrote:

Thanks for the feedback.....I'm glad that worked for you.


I'm not sure if you still had a question about the last zero in the formula
I posted:
B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)

That zero is the 4th argument in the VLOOKUP function and indicates that an
EXACT MATCH is required. (A one would indicate an approximate match)

Simplified example:

With this table in A1:B5....
1 a
2 b
3 c
4 d
2.5 e

C1: =VLOOKUP(2.5,A1:B5,2,0)
That formula (with an exact match) returns "e"

BUT
C1: =VLOOKUP(2.5,A1:B5,2,1)
THAT formula (with an exact match) returns "b"!
An approximate match assumes that the data list is sorted, so in that
instance, it searches until it finds a value larger than the search
value...and returns the previous value. 3 is larger than 2.5, so "b" was
returned....even though 2.5 is further down the list.

I hope that helps
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hi all!

I found Ron's solution to be the easiest for me. It works like a charm
and I couldn't be happier. Question though - what is the last zero in
the formula for? Dave's link helped explain the second to last one.

Bob - your solution would have worked I think but honestly I had
trouble sorting out the pieces. I'm going to keep a copy of it on hand
though, because you never know where it might come in useful.

And Dave - I looked up your link and it helped translate the "0" in
Ron's answer.

Thank you all for your time and efforts in helping. This will save me
a great deal of headache going forward.

Mrs. Rum



Ron Coderre wrote:
Try something like this:

With
The extracted data in Capture!A1:D100
The display column headings in Display!B1:D1
The display row headings in Display!A2:A50

Then, on the Display sheet

B2: =VLOOKUP($A2,Capture!$A$1:$D$100,MATCH(B$1,Capture !$A$1:$D$1,0),0)
Copy that formula down and right to Display!D50

The formula uses a standard VLOOKUP, but uses the MATCH function to return
which column to use.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


" wrote:

Hello!

I'm sure this has been answered ad naseum however I don't know enough
to translate the questions never mind the answers - So I would like to
pose the question in my own way. I hope that someone can help and I
appreciate any direction I receive.

I have a worksheet (Hereafter "Capture") that adds one column of data
each day (via a Microsoft Query connection to Microsoft Access, if that
matters). I then use a 2nd worksheet to display the data (make it
pretty as it were - hereafter "Display")

The information from Access is very simple: My column headings are
dates and my rows are store locations and the intersecting data is the
number of sales for that store on that day. On my "Display" worksheet
I use a combination of HLookup/VLookup to return data from my "Capture"
worksheet. It's functional. But I have to maintain a high level of
trust that things stay where they started when I built the thing.

To fix this I would like to know if there is a function where I can say
to Excel "If this column heading in "Display" matches a column heading
in "Capture" AND this row heading in "Display" matches "Capture" please
return the sales number for that day and store".

Thank you in advance for your time and direction.

Mrs. Rum