Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?

I thought it was barking ... <g

Yes, it is possible to nest VLOOKUPs, and you can also use an
INDEX/MATCH combination to achieve the same.

Is it possible to post a few more details of how your data is
structured and exactly what you want to achieve?

Pete

Shannon wrote:
Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?

Hi Pete,

The data I am using has a series of dates horizontally that I reference,
there are "lines of business" vertically on the left side of the sheet. In a
different workbook I had the dates and lines of business vertically. What I
am trying to do the use the data in the intersecting cell of the first
workbook to populate a cell in the other workbook. So I thought, if I nest a
vlookup, referencing the date and then reference the line of business I could
direct the data. Does that make the dog bark?

"Pete_UK" wrote:

I thought it was barking ... <g

Yes, it is possible to nest VLOOKUPs, and you can also use an
INDEX/MATCH combination to achieve the same.

Is it possible to post a few more details of how your data is
structured and exactly what you want to achieve?

Pete

Shannon wrote:
Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?

I'm still not quite sure what you have. Is it something like this in
sheet1:

Date1 Date2 Date3 etc
Business Line 1 data data data
Business Line 2 data data
Business Line 3 data
Business Line 4 etc

And on this sheet you want to find the "data" that corresponds to a
Business Line and a Date?

Then in your second sheet you have an arrangement like:

Business Line 1 Date1 xx
Business Line 1 Date2
Business Line 1 Date3
etc for other dates
Business Line 2 Date1
Business Line 2 Date2
etc
Business Line 3 Date1
etc etc

and you want xx to be the data from sheet1 which corresponds to
Business Line 1 and Date1 ??

If not, please put me on the right track.

Pete

Shannon wrote:
Hi Pete,

The data I am using has a series of dates horizontally that I reference,
there are "lines of business" vertically on the left side of the sheet. In a
different workbook I had the dates and lines of business vertically. What I
am trying to do the use the data in the intersecting cell of the first
workbook to populate a cell in the other workbook. So I thought, if I nest a
vlookup, referencing the date and then reference the line of business I could
direct the data. Does that make the dog bark?

"Pete_UK" wrote:

I thought it was barking ... <g

Yes, it is possible to nest VLOOKUPs, and you can also use an
INDEX/MATCH combination to achieve the same.

Is it possible to post a few more details of how your data is
structured and exactly what you want to achieve?

Pete

Shannon wrote:
Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?

Yes Pete, that is exactly what I am trying to achieve. Any thoughts?

"Pete_UK" wrote:

I'm still not quite sure what you have. Is it something like this in
sheet1:

Date1 Date2 Date3 etc
Business Line 1 data data data
Business Line 2 data data
Business Line 3 data
Business Line 4 etc

And on this sheet you want to find the "data" that corresponds to a
Business Line and a Date?

Then in your second sheet you have an arrangement like:

Business Line 1 Date1 xx
Business Line 1 Date2
Business Line 1 Date3
etc for other dates
Business Line 2 Date1
Business Line 2 Date2
etc
Business Line 3 Date1
etc etc

and you want xx to be the data from sheet1 which corresponds to
Business Line 1 and Date1 ??

If not, please put me on the right track.

Pete

Shannon wrote:
Hi Pete,

The data I am using has a series of dates horizontally that I reference,
there are "lines of business" vertically on the left side of the sheet. In a
different workbook I had the dates and lines of business vertically. What I
am trying to do the use the data in the intersecting cell of the first
workbook to populate a cell in the other workbook. So I thought, if I nest a
vlookup, referencing the date and then reference the line of business I could
direct the data. Does that make the dog bark?

"Pete_UK" wrote:

I thought it was barking ... <g

Yes, it is possible to nest VLOOKUPs, and you can also use an
INDEX/MATCH combination to achieve the same.

Is it possible to post a few more details of how your data is
structured and exactly what you want to achieve?

Pete

Shannon wrote:
Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?

Sorry for the delay - been out visiting.

You are still reluctant to give me any details, like how many dates or
business lines you have, so I've set up a simple table in Sheet1 as
follows:

01-Jan 02-Jan 03-Jan 04-Jan 05-Jan 06-Jan
Line1 1 2 3 4 5
6
Line2 27 28 29 30 11 12
Line3 13 14 15 16 17 18
Line4 19 40 41 42 43 44

This covers the range A1:G5. In Sheet2 I have built up a table like so:

Line_no Date Value
Line1 01-Jan xx
Line1 02-Jan
Line1 03-Jan
Line1 04-Jan
Line1 05-Jan
Line1 06-Jan
Line2 01-Jan
Line2 02-Jan
Line2 03-Jan
Line2 04-Jan
Line2 05-Jan
Line2 06-Jan
Line3 01-Jan
Line3 02-Jan
Line3 03-Jan
Line3 04-Jan
Line3 05-Jan
Line3 06-Jan
Line4 01-Jan
Line4 02-Jan
Line4 03-Jan
Line4 04-Jan
Line4 05-Jan
Line4 06-Jan yy

where the headings are in row 1. Cell C2 (xx above) contains the
following formula:

=INDEX(Sheet1!$B$2:$G$5,MATCH(A2,Sheet1!A$2:A$5),M ATCH(B2,Sheet1!B$1:G$1))

and this is copied down to cell C25 (yy above), and all the values from
Sheet1 are brought to the corresponding position in this second table.
You will have to adapt the ranges to suit your data, so to help here is
a breakdown of what the formula does:

The first MATCH is looking in the range Sheet1!A2:A5 to find the row
which corresponds to the business line.

The second MATCH is looking in the range Sheet1!B1:G1 trying to find
which column the date matches with.

The INDEX function uses the row and column information to extract the
appropriate data value from the range Sheet1!B2:G5.

I think this is what you want.

Hope this helps.

Pete


Shannon wrote:
Yes Pete, that is exactly what I am trying to achieve. Any thoughts?

"Pete_UK" wrote:

I'm still not quite sure what you have. Is it something like this in
sheet1:

Date1 Date2 Date3 etc
Business Line 1 data data data
Business Line 2 data data
Business Line 3 data
Business Line 4 etc

And on this sheet you want to find the "data" that corresponds to a
Business Line and a Date?

Then in your second sheet you have an arrangement like:

Business Line 1 Date1 xx
Business Line 1 Date2
Business Line 1 Date3
etc for other dates
Business Line 2 Date1
Business Line 2 Date2
etc
Business Line 3 Date1
etc etc

and you want xx to be the data from sheet1 which corresponds to
Business Line 1 and Date1 ??

If not, please put me on the right track.

Pete

Shannon wrote:
Hi Pete,

The data I am using has a series of dates horizontally that I reference,
there are "lines of business" vertically on the left side of the sheet. In a
different workbook I had the dates and lines of business vertically. What I
am trying to do the use the data in the intersecting cell of the first
workbook to populate a cell in the other workbook. So I thought, if I nest a
vlookup, referencing the date and then reference the line of business I could
direct the data. Does that make the dog bark?

"Pete_UK" wrote:

I thought it was barking ... <g

Yes, it is possible to nest VLOOKUPs, and you can also use an
INDEX/MATCH combination to achieve the same.

Is it possible to post a few more details of how your data is
structured and exactly what you want to achieve?

Pete

Shannon wrote:
Hello,
Say I was wondering if it is possible to nest vlookup statements. I am in
one workbook and I am trying to reference a date and then reference a
different cell, if both cells match the reference cells then populate the
data from the intersecting cell. It does work with If/Then, but it seems
like vlookup would be much more efficient. Am I looking up the wrong
proverbial tree?