vlookup with if statement - Excel 2003

[murkaboris]

Hello:

I have a worksheet with the following columns:

ID Angio AW CT Mammo MR NUC IIS
403 1200.0 379.0 1890.0 338.7 2267.0 220.0 1800.0
399 0.0 466.5 3000.0 301.2 3800.4 200.3 1200.0
406 0.0 207.6 1853.0 330.0 1762.0 300.0 2300.0
408 0.0 308.0 1082.1 288.0 1221.7 264.4 2257.5
407 0.0 570.0 3050.0 630.0 3200.0 300.0 2200.0
404 1000.0 382.0 1780.0 300.0 2200.0 320.0 800.0

I need to lookup the territory ID and then return the value in the columns
based on their column header (Angio, AW, Ct, etc...) in the following format
on another worksheet:

403 2009 OP
CT
MR
Mammo
RAD
R&F
Nuc
PET
AW
Core Total 0.0
Angio
IIS
Total 0.0

....another territory the same format:
399 2009 OP
CT
MR
Mammo
RAD
R&F
Nuc
PET
AW
Core Total 0.0
Angio
IIS
Total 0.0


Please help.
Thank you.

Monika

 

[smartin]

Hello:

I have a worksheet with the following columns:

ID Angio AW CT Mammo MR NUC IIS
403 1200.0 379.0 1890.0 338.7 2267.0 220.0 1800.0
399 0.0 466.5 3000.0 301.2 3800.4 200.3 1200.0
406 0.0 207.6 1853.0 330.0 1762.0 300.0 2300.0
408 0.0 308.0 1082.1 288.0 1221.7 264.4 2257.5
407 0.0 570.0 3050.0 630.0 3200.0 300.0 2200.0
404 1000.0 382.0 1780.0 300.0 2200.0 320.0 800.0

I need to lookup the territory ID and then return the value in the columns
based on their column header (Angio, AW, Ct, etc...) in the following format
on another worksheet:

403 2009 OP
CT
MR
Mammo
RAD

Hi Monika,

A combination of INDEX and MATCH can do this. Assume your first table is
cornered at Sheet1 A1:H7 and the lookup area is at Sheet2 A1:Axx, then
put the following formula in Sheet2 B2 and fill down. Sorry this will
probably suffer line wrap in this post:

=INDEX(Sheet1!$B$2:$H$7,MATCH(Sheet2!$A$1,Sheet1!$A$2:$A$7,0),MATCH(Sheet2!$A2,Sheet1!$B$1:$H$1,0))

 

[MyVeryOwnSelf]

I have a worksheet with the following columns:

ID Angio AW CT Mammo MR NUC IIS
403 1200.0 379.0 1890.0 338.7 2267.0 220.0
1800.0 399 0.0 466.5 3000.0 301.2 3800.4 200.3
1200.0 406 0.0 207.6 1853.0 330.0 1762.0
300.0 2300.0 408 0.0 308.0 1082.1 288.0 1221.7
264.4 2257.5 407 0.0 570.0 3050.0 630.0
3200.0 300.0 2200.0 404 1000.0 382.0 1780.0
300.0 2200.0 320.0 800.0

I need to lookup the territory ID and then return the value in the
columns based on their column header (Angio, AW, Ct, etc...) in the
following format on another worksheet:

403 2009 OP
CT
MR
Mammo
RAD
R&F
Nuc
PET
AW
Core Total 0.0
Angio
IIS
Total 0.0

...another territory the same format:
399 2009 OP
CT
MR
Mammo
RAD
R&F
Nuc
PET
AW
Core Total 0.0
Angio
IIS
Total 0.0

Here's one way, starting with the original data on Sheet1.

On Sheet2, each territory would use 14 lines, including a blank line
between them for readability.

Working on Sheet2, start by putting this in A1:
=OFFSET(Sheet1!$A$1,INT(ROW()/14)+1,0)

Then put 2009 and OP in B1 and C1.

Then put CT, MR, etc., in their proper places in A2:A13, as text constants.
Likewise, put "Core Total" and "Total" in A10 and A13.

Next put this in B2:
=OFFSET(Sheet1!$A$1,INT(ROW()/14)+1,MATCH($A2,Sheet1!$1:$1,0)-1)
B2 can be copied and pasted into each place in B3:B12 where a value from
Sheet1 needs to be fetched. (The columns RAD, R&F, and Pet don't appear in
the original data -- this appears to be an oversight; I assume it'll be
fixed before proceeding.)

The SUM()s for Core Total and Total are straightforward.

Once the first territory is done right on Sheet2, select rows 1:14 there
and use
Edit > Copy
Then select A15 and
Edit > Paste

The 14 rows can be pasted again and again for the consecutive territories.

Adjust to suit the detailed needs.

 

[murkaboris]

Hello:

Thank you for the formula, it didn't quite work meaning it returned
incorrect value.
For better representation here is the formula as it applies to my two sheets:


=INDEX('[ATF master file.xls]Orders OP'!$E$2:$O$55,MATCH($A$3,'[ATF master
file.xls]Orders OP'!$D$3:$D$54,0),MATCH($A$9,'[ATF master file.xls]Orders
OP'!$E$2:$O$2,0))

....so on my file the Territory ID is always in A3 on the 2nd sheet; The
modalities on the first sheet (Angio, etc...)starts in column "E" and on that
xls the Territory ID is in column "D"

Please advise.
Thank you.
Monika

 

[murkaboris]

Hello:

It didn't work for me as the placement of the ID on the 2nd workbook is very
important for the final result.......so on my file the ID is always in A3 on
the 2nd workbook and the modalities then start on A9; The modalities on the
first workbook(Angio, etc...)starts in column "E" and on that xls the ID is
in column "D". The 2nd workbook has a worksheet for each ID. So the next ID
will have a new worksheet within the workbook with the same placement....

Any advise?
Thank you.

Monika

 

[MyVeryOwnSelf]

It didn't work for me as the placement of the ID on the 2nd workbook

is very important for the final result.......so on my file the ID is
always in A3 on the 2nd workbook and the modalities then start on A9;

The modalities on the first workbook(Angio, etc...)starts in column
"E" and on that xls the ID is in column "D". The 2nd workbook has a
worksheet for each ID. So the next ID will have a new worksheet within
the workbook with the same placement....

Maybe this will get things started.

Use [Book1.xls]Sheet1 as the first workbook.

Use [Book2.xls]Sheet1, Sheet2, etc., as the second workbook. Before
starting make sure Book2 has been Saved at least once.

Start working on [Book2.xls]Sheet1.

In A3 there put
=OFFSET([Book1.xls]Sheet1!$D$1,
SUBSTITUTE(
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),
"Sheet",""),
0)
This is a bit intricate. It takes the tab name where it resides and
strips off "Sheet" to get the tab number, then it uses that number to
select an ID from Book1.

In A4:A11 and A13:A14 put the modality names.

Put "Core Total" and "Total" and "2009" and "OP" where expected.

In B4 put
=OFFSET([Book1.xls]Sheet1!$A$1,
MATCH($A$3,[Book1.xls]Sheet1!$D:$D,0)-1,
MATCH($A4, [Book1.xls]Sheet1!$1:$1,0)-1)
This does a 2-D table lookup in Book1 using the ID to select a row and
the modality name to select a column.

Copy B4 and paste it immediately to the right of each modality name.

In B12 put
=SUM(B4:B11)

In B15 put
=SUM(B12:B14)

This should complete [Book2.xls]Sheet1. Now for the remaining sheets.

Click in the upper-left corner of [Book2.xls]Sheet1 to select the entire
sheet (or just hit ctrl-A). Then use
Edit > Copy

Click on the tab for [Book2.xls]Sheet2, select the entire sheet and use
Edit > Paste

Click on the tab for [Book2.xls]Sheet3, select the entire sheet and use
Edit > Paste

Likewise for Sheet3, Sheet4, etc.

Hope this helps. If the data arrangement still isn't exactly right, try
starting with the above then manipulating it into the right layout.

 

[murkaboris]

THANK YOU!!!
With a few format adjustments it worked like a magic.

Monika

It didn't work for me as the placement of the ID on the 2nd workbook
is very important for the final result.......so on my file the ID is
always in A3 on the 2nd workbook and the modalities then start on A9;

The modalities on the first workbook(Angio, etc...)starts in column
"E" and on that xls the ID is in column "D". The 2nd workbook has a
worksheet for each ID. So the next ID will have a new worksheet within
the workbook with the same placement....

Maybe this will get things started.

Use [Book1.xls]Sheet1 as the first workbook.

Use [Book2.xls]Sheet1, Sheet2, etc., as the second workbook. Before
starting make sure Book2 has been Saved at least once.

Start working on [Book2.xls]Sheet1.

In A3 there put
=OFFSET([Book1.xls]Sheet1!$D$1,
SUBSTITUTE(
MID(CELL("filename"),FIND("]",CELL("filename"))+1,255),
"Sheet",""),
0)
This is a bit intricate. It takes the tab name where it resides and
strips off "Sheet" to get the tab number, then it uses that number to
select an ID from Book1.

In A4:A11 and A13:A14 put the modality names.

Put "Core Total" and "Total" and "2009" and "OP" where expected.

In B4 put
=OFFSET([Book1.xls]Sheet1!$A$1,
MATCH($A$3,[Book1.xls]Sheet1!$D:$D,0)-1,
MATCH($A4, [Book1.xls]Sheet1!$1:$1,0)-1)
This does a 2-D table lookup in Book1 using the ID to select a row and
the modality name to select a column.

Copy B4 and paste it immediately to the right of each modality name.

In B12 put
=SUM(B4:B11)

In B15 put
=SUM(B12:B14)

This should complete [Book2.xls]Sheet1. Now for the remaining sheets.

Click in the upper-left corner of [Book2.xls]Sheet1 to select the entire
sheet (or just hit ctrl-A). Then use
Edit > Copy

Click on the tab for [Book2.xls]Sheet2, select the entire sheet and use
Edit > Paste

Click on the tab for [Book2.xls]Sheet3, select the entire sheet and use
Edit > Paste

Likewise for Sheet3, Sheet4, etc.

Hope this helps. If the data arrangement still isn't exactly right, try
starting with the above then manipulating it into the right layout.

 

[MyVeryOwnSelf]

THANK YOU!!!

With a few format adjustments it worked like a magic.

Y'welcome.

But I just realized that the B4 formula wouldn't work right if any of the
IDs in Book1 column D can be repeated. If that's possible, B4 can choose a
row in Book1 the same way A3 does, rather than looking for the ID.