Simplify VLOOKUP and IF Formula?

[jeffcoleky]

I created the formula below that does the following: Display "Yes" if the address is For Sale, and if not, check to see if it's Vacant, and if so display "TRUE". Otherwise leave cell empty. I even have a working formula...

Here's my sample data:

Excel 2010

	A	B	C	D	E
DATA
LOOKUPS	Vacant Lookup	For Sale Lookup

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]Vacant(normally in another workbook)[/TD]
[TD="align: center"]For Sale
(normally in another Sheet)[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="bgcolor: #C5D9F1"]111 main st[/TD]
[TD="bgcolor: #C5D9F1, align: center"]TRUE[/TD]
[TD="bgcolor: #C5D9F1, align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="bgcolor: #C5D9F1"]222 smith rd[/TD]
[TD="bgcolor: #C5D9F1, align: center"]FALSE[/TD]
[TD="bgcolor: #C5D9F1, align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #C5D9F1"]333 sammy rd[/TD]
[TD="bgcolor: #C5D9F1, align: center"]TRUE[/TD]
[TD="bgcolor: #C5D9F1, align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="bgcolor: #C5D9F1"]678 Ninth St[/TD]
[TD="bgcolor: #C5D9F1, align: center"]FALSE[/TD]
[TD="bgcolor: #C5D9F1, align: center"]No[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"][/TD]
[TD="align: center"]Current Formula[/TD]

[TD="align: center"]8[/TD]
[TD="bgcolor: #E4DFEC"]111 main st[/TD]
[TD="bgcolor: #E4DFEC, align: center"]TRUE[/TD]
[TD="bgcolor: #E4DFEC, align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBF1DE, align: center"]Yes[/TD]

[TD="align: center"]9[/TD]
[TD="bgcolor: #E4DFEC"]222 smith rd[/TD]
[TD="bgcolor: #E4DFEC, align: center"]FALSE[/TD]
[TD="bgcolor: #E4DFEC, align: center"]Yes[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBF1DE, align: center"]Yes[/TD]

[TD="align: center"]10[/TD]
[TD="bgcolor: #E4DFEC"]333 sammy rd[/TD]
[TD="bgcolor: #E4DFEC, align: center"]TRUE[/TD]
[TD="bgcolor: #E4DFEC, align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBF1DE, align: center"]TRUE[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: #E4DFEC"]555 Notfoundabove Rd[/TD]
[TD="bgcolor: #E4DFEC, align: center"]#N/A[/TD]
[TD="bgcolor: #E4DFEC, align: center"]#N/A[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #EBF1DE, align: center"][/TD]

</tbody>

Sheet1​

B8 Formula:

=VLOOKUP($A8,A$2:C$5,2,FALSE)

C8 Formula:

=VLOOKUP($A8,A$2:C$5,3,FALSE)

E8 Formula, (desired result) is working when combined with the other two formulas:

=IF(ISNA(C8),IF(ISNA(B8),"",IF(C8="Yes",C8,IF(B8="TRUE","TRUE",""))),IF(C8=0,IF(B8=0,"",B8),C8))

My formulas in C8:C11 are working! However, my problem is that my ACTUAL data doesn't look this clean. My actual data doesn't have the Lookup fields in B8:C11. It looks more like A2:C5 + A8:A11. I need help to COMBINE my formula in E8 with the formulas in B8 & C8.

Doing so the only way I know how to, looks like this:

=IF(ISNA(VLOOKUP($A8,A$2:C$5,3,FALSE)),IF(ISNA(VLOOKUP($A8,A$2:C$5,2,FALSE)),"",IF(VLOOKUP($A8,A$2:C$5,3,FALSE)="Yes",VLOOKUP($A8,A$2:C$5,3,FALSE),IF(VLOOKUP($A8,A$2:C$5,2,FALSE)="TRUE","TRUE",""))),IF(VLOOKUP($A8,A$2:C$5,3,FALSE)=0,IF(VLOOKUP($A8,A$2:C$5,2,FALSE)=0,"",VLOOKUP($A8,A$2:C$5,2,FALSE)),VLOOKUP($A8,A$2:C$5,3,FALSE)))

Is there a shorter way to write this formula?

With my actual data, the values I'm looking up using VLOOKUP, are actually located in other workbooks too! So the above formula, combined with all the paths, filenames, etc. makes this thing look like a monster formula!

I've spent HOURS trying to shorten this formula today, but with no success.. Can anyone help?

 

[bschwartz]

Try this: =IFERROR(IF(IF(ISNUMBER(MATCH(A8,$A$2:$A$5,0)),VLOOKUP(A8,$A$2:$C$5,3,0))="Yes","Yes",IF(VLOOKUP(A8,$A$14:$B$17,2,0)=TRUE,TRUE,"")),"")

 

[jeffcoleky]

Try this: =IFERROR(IF(IF(ISNUMBER(MATCH(A8,$A$2:$A$5,0)),VLOOKUP(A8,$A$2:$C$5,3,0))="Yes","Yes",IF(VLOOKUP(A8,$A$14:$B$17,2,0)=TRUE,TRUE,"")),"")

Perfect! Yay! Sorry for the confusion and I appreciate you adapting. Your help & time spent are greatly appreciated!

 

[bschwartz]

Not a problem. Glad I could help.