I have a spreadsheet with multiple worksheets. I need one sheet called
Customer Lookup to pull data from another sheet called Liability Policy
Expiration, which has many columns of data. There are 26 columns I need to
pull data from bases on a customer number.

The customer number is in column B. When that number is entered into the
lookup sheet I need to pull the following columns: A, C, D, AI, AJ, AQ, AR,
AY, AZ, BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD, DK, DL.

Here is the formula I tried to pull column A:
=IF(ISNA(VLOOKUP(B1,'Liability Policy Expiration'!$A$6:$DN$464,1,FALSE)),0,
VLOOKUP(B1,'Liability Policy Expiration'!$A$6:$DN$464,1,FALSE))

The result I get is a 0. No errors, so I guess the syntax is correct. B1
is where I entered the customer number I want the data for.

What do I need to change to make this work?
Thanks,
Dan

The formula is returning 0 because the lookup value hasn't been found.

You say the customer number is in column B but your formula is looking for
that number in column A. Another issue is that Vlookup only works to the
RIGHT but you want to return a value from column A which is to the LEFT of
column B.

So, you either need separate formulas, one for column A and one for all the
other columns or, move column B so that it is the LEFTMOST column of the
table.

Let us know what you want to do, separate formulas or reconfigure your
table.

Biff

"Dan B" wrote in message
...
I have a spreadsheet with multiple worksheets. I need one sheet called
Customer Lookup to pull data from another sheet called Liability Policy
Expiration, which has many columns of data. There are 26 columns I need to
pull data from bases on a customer number.

The customer number is in column B. When that number is entered into the
lookup sheet I need to pull the following columns: A, C, D, AI, AJ, AQ,
AR, AY, AZ, BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD, DK,
DL.

Here is the formula I tried to pull column A:
=IF(ISNA(VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE)),0, VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE))

The result I get is a 0. No errors, so I guess the syntax is correct. B1
is where I entered the customer number I want the data for.

What do I need to change to make this work?
Thanks,
Dan

I will swap places with column A and B. That sounds like the easiest thing
to do. So Column A will now have the Customer Number and B will have the
customer name. So, in that case, how can I do this?

thanks!!


"Biff" wrote in message
...
The formula is returning 0 because the lookup value hasn't been found.

You say the customer number is in column B but your formula is looking for
that number in column A. Another issue is that Vlookup only works to the
RIGHT but you want to return a value from column A which is to the LEFT of
column B.

So, you either need separate formulas, one for column A and one for all
the other columns or, move column B so that it is the LEFTMOST column of
the table.

Let us know what you want to do, separate formulas or reconfigure your
table.

Biff

"Dan B" wrote in message
...
I have a spreadsheet with multiple worksheets. I need one sheet called
Customer Lookup to pull data from another sheet called Liability Policy
Expiration, which has many columns of data. There are 26 columns I need
to pull data from bases on a customer number.

The customer number is in column B. When that number is entered into the
lookup sheet I need to pull the following columns: A, C, D, AI, AJ, AQ,
AR, AY, AZ, BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD, DK,
DL.

Here is the formula I tried to pull column A:
=IF(ISNA(VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE)),0, VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE))

The result I get is a 0. No errors, so I guess the syntax is correct.
B1 is where I entered the customer number I want the data for.

What do I need to change to make this work?
Thanks,
Dan

With the swap done, since you have a lot of irregular cols to return,
... need to pull the following columns: A, C, D, AI, AJ, AQ, AR, AY, AZ,
BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD, DK, DL.

one way is to use an incrementer for the col index part of the vlookup in
the startcell, say: COLUMN(B1)

The above simply returns 2 (ie the return col for the startcell), and then
increments accordingly: 3,4,5 ... as the startcell's formula is copied
across, giving us the corresponding returns from the other cols.

Try this slightly revised version in the startcell C1:
=IF(ISNA(MATCH($B1,'Liability Policy Expiration'!$A$6:$A$464,0)),0,
VLOOKUP($B1,'Liability Policy Expiration'!$A$6:$DN$464,COLUMN(B1),0))

C1 should return the cust name corresponding to the cust no. in B1. Then
just copy C1 right across to DL1, fill down as far as required to pull in
all the returns. "Clean up" by manually hiding the irrelevant cols between
cols C and DL that's not wanted.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" <dkbrim-at-yahoo-dot-com wrote in message
...
I will swap places with column A and B. That sounds like the easiest thing
to do. So Column A will now have the Customer Number and B will have the
customer name. So, in that case, how can I do this?

Great suggestion. That will save some time! I'll try that one.
Thank You!

Dan

"Max" wrote in message
...
With the swap done, since you have a lot of irregular cols to return,
... need to pull the following columns: A, C, D, AI, AJ, AQ, AR, AY, AZ,
BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD, DK, DL.

one way is to use an incrementer for the col index part of the vlookup in
the startcell, say: COLUMN(B1)

The above simply returns 2 (ie the return col for the startcell), and then
increments accordingly: 3,4,5 ... as the startcell's formula is copied
across, giving us the corresponding returns from the other cols.

Try this slightly revised version in the startcell C1:
=IF(ISNA(MATCH($B1,'Liability Policy Expiration'!$A$6:$A$464,0)),0,
VLOOKUP($B1,'Liability Policy Expiration'!$A$6:$DN$464,COLUMN(B1),0))

C1 should return the cust name corresponding to the cust no. in B1. Then
just copy C1 right across to DL1, fill down as far as required to pull in
all the returns. "Clean up" by manually hiding the irrelevant cols between
cols C and DL that's not wanted.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" <dkbrim-at-yahoo-dot-com wrote in message
...
I will swap places with column A and B. That sounds like the easiest
thing to do. So Column A will now have the Customer Number and B will
have the customer name. So, in that case, how can I do this?

You're welcome, Dan. Hope you're still tracking your thread, don't miss
Biff's response <g
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Dan B" <dkbrim-at-yahoo-dot-com wrote in message
...
Great suggestion. That will save some time! I'll try that one.
Thank You!

Dan

Ok, you have a very difficult pattern to sequence.

For the first 3 cells:

=IF(ISNA(MATCH($B1,'Liability Policy Expiration'!$A$6:$A$464,0)),0,
VLOOKUP($B1,'Liability Policy Expiration'!$A$6:$DN$464,COLUMNS($A:B),0))

Copy across 3 cells.

For the rest (starting in the 4th cell):

=IF(ISNA(MATCH($B1,'Liability Policy
Expiration'!$A$6:$A$464,0)),0,VLOOKUP($B1,'Liabili ty Policy
Expiration'!$A$6:$DN$464,IF(COLUMNS($A:A)=1,35,COL UMNS($A:AI)+CEILING((COLUMNS($A:A)-2)/2,1)*6),0))

Copy across the remaining 22 cells.

Biff

"Dan B" <dkbrim-at-yahoo-dot-com wrote in message
...
I will swap places with column A and B. That sounds like the easiest thing
to do. So Column A will now have the Customer Number and B will have the
customer name. So, in that case, how can I do this?

thanks!!


"Biff" wrote in message
...
The formula is returning 0 because the lookup value hasn't been found.

You say the customer number is in column B but your formula is looking
for that number in column A. Another issue is that Vlookup only works to
the RIGHT but you want to return a value from column A which is to the
LEFT of column B.

So, you either need separate formulas, one for column A and one for all
the other columns or, move column B so that it is the LEFTMOST column of
the table.

Let us know what you want to do, separate formulas or reconfigure your
table.

Biff

"Dan B" wrote in message
...
I have a spreadsheet with multiple worksheets. I need one sheet called
Customer Lookup to pull data from another sheet called Liability Policy
Expiration, which has many columns of data. There are 26 columns I need
to pull data from bases on a customer number.

The customer number is in column B. When that number is entered into
the lookup sheet I need to pull the following columns: A, C, D, AI, AJ,
AQ, AR, AY, AZ, BG, BH, BO, BP, BW, BX, CE, CF, CM, CN, CU, CV, DC, DD,
DK, DL.

Here is the formula I tried to pull column A:
=IF(ISNA(VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE)),0, VLOOKUP(B1,'Liability Policy
Expiration'!$A$6:$DN$464,1,FALSE))

The result I get is a 0. No errors, so I guess the syntax is correct.
B1 is where I entered the customer number I want the data for.

What do I need to change to make this work?
Thanks,
Dan