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
1	DATA	Vacant(normally in another workbook)	For Sale (normally in another Sheet)
2	111 main st	TRUE	Yes
3	222 smith rd	FALSE	Yes
4	333 sammy rd	TRUE
5	678 Ninth St	FALSE	No
6
7	LOOKUPS	Vacant Lookup	For Sale Lookup		Current Formula
8	111 main st	TRUE	Yes		Yes
9	222 smith rd	FALSE	Yes		Yes
10	333 sammy rd	TRUE	0		TRUE
11	555 Notfoundabove Rd	#N/A	#N/A

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