Formula Confusion

[Guest]

=IF(ISNA(VLOOKUP(A1,'[1 Acct Number.xls]ALL
1900''s'!$A$2:$K$273,10,FALSE)),"",IF(VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE)="","",VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE)))

Plain english - if the answer is not in column 10 then look it up in column
11 , if both columns are blank then blank

with the formula above , if there is an answer in column 11 it's OK but if
there is an answer in column 10 I get blank . the answer is either in cloumn
10 or column 11

 

[Don Guillett]

Modify this idea to suit. If in col 2, OK, else look in col 3).
=VLOOKUP(I4,I5:L7,IF(VLOOKUP(I4,I5:L7,2)=0,3,2))
You may also want to name your lookup range to make it easier.

=VLOOKUP(I4,myrng,IF(VLOOKUP(I4,myrng,2)=0,3,2))

 

[Guest]

try:

=IF(ISNA(VLOOKUP(A1,'[1 Acct Number.xls]ALL
1900''s'!$A$2:$K$273,10,FALSE)),IF(ISNAVLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE))," ",VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE)),VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,10,FALSE))

 

[Don Guillett]

try this
Insert>name>define>name it myrng>in the refers to box
'[1 Acct Number.xls]ALL1900''s'!$A$2:$K$273
=VLOOKUP(I4,myrng,IF(VLOOKUP(I4,myrng,2)=0,3,2),0)


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Modify this idea to suit. If in col 2, OK, else look in col 3).
=VLOOKUP(I4,I5:L7,IF(VLOOKUP(I4,I5:L7,2)=0,3,2))
You may also want to name your lookup range to make it easier.

=VLOOKUP(I4,myrng,IF(VLOOKUP(I4,myrng,2)=0,3,2))
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

=IF(ISNA(VLOOKUP(A1,'[1 Acct Number.xls]ALL
1900''s'!$A$2:$K$273,10,FALSE)),"",IF(VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE)="","",VLOOKUP(A1,'[Acct Number.xls]ALL
1900''s'!$A$2:$K$273,11,FALSE)))

Plain english - if the answer is not in column 10 then look it up in
column
11 , if both columns are blank then blank

with the formula above , if there is an answer in column 11 it's OK but
if
there is an answer in column 10 I get blank . the answer is either in
cloumn
10 or column 11