Can I combine multiple vlookups in one formula?

[Didymus]

Hi! First time posting, but have searched the forum and not found an answer to exactly what I'm trying to do. Maybe it's not possible.

I am using this formula
=IF(SEARCH("pipe",A2),VLOOKUP(C2,Sheet2!A4:B12,2))
to look up a reference code for "pipe". The reference codes for "pipe" are in column 2 of a table on sheet 2 and are dependant on the dimensions of the pipe given in column 1 of the same table. C2 in the formula is where the dimensions are given. (hope that's clear ).

What I want to do is to extend the above formula to look up column 3 of the table on sheet 2 if, for example, A2 contains "Bend", i.e., =IF(SEARCH("Bend",A2),VLOOKUP(C2,Sheet2!A4:B12,3)).

Combining those two formula using "=if(and " worked for me for the first test, but changing the contents of A2 from "pipe" to "Bend" results in a #Value type error.

I can't use if A2 = "pipe" because extra words could be introduced into A2, e.g., "copper pipe" - therefore the equals would not be appropriate.

Any guidance will be much appreciated.

 

[jaaakez99]

This is my first posting. So, I hope I am doing this correctly. I need similar help with multiple vlookups. I want to add 2 vlookup outcomes together into one cell. The formula I currently have set up is as follows:

<TABLE style="WIDTH: 504pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=839><COLGROUP><COL style="WIDTH: 222pt; mso-width-source: userset; mso-width-alt: 10825" width=370><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339" width=183><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5193" width=178><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145" width=108><TBODY><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 504pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8; mso-ignore: colspan" id=td_post_1690820 height=25 width=839 colSpan=4>=IF(AND(TODAY()>=$H$3,OR(J27>0,K27>0)),VLOOKUP(CashonHand!A199,CashonHand!$6:$201,5,0),"")</TD></TR></TBODY></TABLE>

I want to revise the above formula to look like the below formula by including the underlined vlookup in the formula.

=IF(AND(TODAY()>=$H$3,OR(J27>0,K27>0)),VLOOKUP(CashonHand!A199,CashonHand!$6:$201,5,0),VLOOKUP(CashonHand!A199,CashonHand!$6:$201,5,0),"")

My goal is to to sum the 2 vlookups or display an empty cell.

The range from the CashonHand tab is as follows:

<TABLE style="WIDTH: 649pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1081><COLGROUP><COL style="WIDTH: 265pt; mso-width-source: userset; mso-width-alt: 12909" width=441><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 4681" span=4 width=160><TBODY><TR style="HEIGHT: 30pt" height=50><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 265pt; HEIGHT: 30pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl63 height=50 width=441>Account</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=160>Bi-Weekly Budget - Seasonalized 1 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=160>Bi-Weekly Budget - Seasonalized 2 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl64 width=160>YTD </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl65 width=160>1/8/2010</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>SubTotals</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Beginning Balance</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Deposits (Withdrawals from Bank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 2,000,000.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 4,500,000.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 2,750,000.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 2,750,000.00 </TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Deposits from Cash (not removed from Bank)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ - </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ - </TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Withdrawals (Deposited in Bank)TOTAL</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ (100,000.00)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ (100,000.00)</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Withdrawals (Not Deposited in Bank)TOTAL</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ (50,000.00)</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ (50,000.00)</TD></TR><TR style="HEIGHT: 15pt" height=25><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=25>Ending Balance</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 2,600,000.00 </TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" class=xl66>$ 2,600,000.00 </TD></TR></TBODY></TABLE>

Is it possible to get this formula to work (obviously the above formula isnt working).