If/Vlookup

[C.R.]

Hello,
I have 10 columns, grouped in 2's. Each 2 columns is a day of the week. Under each day is a Sequence # column and an account # column. I want to type in an account # in cell K3 and, using
=IF(VLOOKUP(K3,B3:B7,1,FALSE),"Monday Account",IF(VLOOKUP(K3,D3:D7,1,FALSE),"Tuesday Account",IF(VLOOKUP(K3,F3:F7,1,FALSE),"Wednesday Account",IF(VLOOKUP(K3,H3:H7,1,FALSE),"Thursday Account",IF(VLOOKUP(K3,J3:J7,1,FALSE),"Friday Account")))))

This only works for Monday accounts. Any other account #'s entered gets a #N/A error. I am stumped.

Thanks in advance,

C.R.

 

[Superstar31]

Can I see your workbook? or an example of it?

 

[colbymack]

You can keep you same general format if you do something like this:

=IF(NOT(ISERROR(VLOOKUP(K3,B3:B7,1,FALSE))),"Monday Account",IF(NOT(ISERROR(VLOOKUP(K3,D3:D7,1,FALSE))),"Tuesday Account",IF(NOT(ISERROR(VLOOKUP(K3,F3:F7,1,FALSE))),"Wednesday Account",IF(NOT(ISERROR(VLOOKUP(K3,H3:H7,1,FALSE))),"Thursday Account","Friday Account"))))

The issue is that the first vlookup is producing the #N/A error, which is causing the rest of the formula to bomb out. By putting in the Not(iserror(, you are handling the #N/A and telling it to go on to the next argument. The process assumes you have a value in K3. If not, you will still get Friday Account. This can be handled as well if this is not desireable by first doing a check to make sure that K3 <>"".

HTH
Colbymack

 

[Superstar31]

****, thats twice i was beaten before i could help somone out

 

[C.R.]

Colbymack,

I could see that the formula was crapping out but was clueless as to how to handle it. Many thanks. Your solution works perfectly. My headache is gone now.

C.R.

 

[colbymack]

Superstar - it happens to me all the time!!!

 

[Aladin Akyurek]

Hello,
I have 10 columns, grouped in 2's. Each 2 columns is a day of the week. Under each day is a Sequence # column and an account # column. I want to type in an account # in cell K3 and, using
=IF(VLOOKUP(K3,B3:B7,1,FALSE),"Monday Account",IF(VLOOKUP(K3,D3:D7,1,FALSE),"Tuesday Account",IF(VLOOKUP(K3,F3:F7,1,FALSE),"Wednesday Account",IF(VLOOKUP(K3,H3:H7,1,FALSE),"Thursday Account",IF(VLOOKUP(K3,J3:J7,1,FALSE),"Friday Account")))))

This only works for Monday accounts. Any other account #'s entered gets a #N/A error. I am stumped.

Thanks in advance,

C.R.

More compact and less expensive to invoke:

=INDEX({"Monday Account","","Tuesday Account","","Wednesday Account","","Thursday Account","","Friday Account"},MIN(IF((MOD(COLUMN($B$3:$J$7)-COLUMN($B$3)+0,2)=0)*($B$3:$J$7=K3),COLUMN($B$3:$J$7)-COLUMN($B$3)+1)))

which must be confirmed with control+shift+enter, not just with enter.

 

[C.R.]

Aladin,

Many thanks. I'll gave that most elegant solution a try tomorrow. Wow.

C.R.