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]

Does this work?

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

 

[jeffcoleky]

I LOVE how short it is. However, i'd like it to ONLY display results if "Yes" or "TRUE". For instance, if C2="No" and B2="TRUE", i'd want it to display "TRUE". Any idea how to add that to your formula?

Edit: I just noticed my original formula didn't take that into account either.

 

[bschwartz]

As far as I know it already works that way. Did you test it on that example and get different results?

 

[jeffcoleky]

Strange, I did test it before my previous post. However, I double checked and it worked.... I don't know what i did wrong the fist time incorrectly but I believe you got it. Thank you!!!

 

[bschwartz]

You're very welcome.

 

[jeffcoleky]

Ben, I'll gladly start a new thread if necessary, but I just realized something about my data that I was unclear about above, and that situation breaks the formula.

My data in A2:B5 is actually located in two different locations. Here's how i should have written the example above:

Excel 2010
	A	B	C	D	E
1	DATA		For Sale (normally in another Sheet)
2	111 main st		Yes
3	222 smith rd		Yes
4	333 sammy rd
5	678 Ninth St		No
6
7	LOOKUPS	Vacant Lookup	For Sale Lookup		Current Formula
8	111 main st	TRUE	Yes		Yes
9	222 smith rd	TRUE	Yes		Yes
10	333 sammy rd	TRUE	0		TRUE
11	555 Notfoundabove Rd	#N/A	#N/A
12
13	DATA	Vacant (normally in another workbook)
14	111 main st	TRUE
15	222 smith rd	TRUE
16	333 sammy rd	TRUE
17	678 Ninth St	FALSE
Sheet1

Basically the vacant section moved down....

Here's how I modified your formula to adapt:

=IFERROR(IF(VLOOKUP($A8,A$2:C$5,3,FALSE)="Yes","Yes",IF(VLOOKUP($A8,A$14:B$17,2,FALSE)=TRUE,TRUE,"")),"")

The formula breaks when the formula cannot lookup an address in the A2:A5 range. In that situation it ignores the address found in A14:A17.

Do you have any ideas how it could be adapted or should I start a new thread for this?

 

[bschwartz]

Your formula looks good to me. What do you mean by "breaks when the formula cannot lookup an address in the A2:A5 range?"

In your example, it seems to be working, otherwise 333 Sammy Rd would be blank. Whereas you are returning TRUE.

 

[jeffcoleky]

meaning, if you delete the value in A2

 

[bschwartz]

I didn't realize the two tables were different lists, I thought they contained the same addresses, but one had the On Sale column, and the other contained the Vacant column.

I'll take another stab at it later.