What is wrong with this formula?

[Dan B]

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

 

[Biff]

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]

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!!

 

[Max]

With the swap done, since you have a lot of irregular cols to return, re:

... 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.

---

 

[Dan B]

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

Dan

 

[Biff]

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,'Liability Policy
Expiration'!$A$6:$DN$464,IF(COLUMNS($A:A)=1,35,COLUMNS($A:AI)+CEILING((COLUMNS($A:A)-2)/2,1)*6),0))

Copy across the remaining 22 cells.

Biff

 

[Max]

You're welcome, Dan. Hope you're still tracking your thread, don't miss
Biff's response <g>