Using a nested IF Function and AND function on 2 sheets?

=IF($G5=VLOOKUP($B5,Sheet2!$A$6:$E$10,5,0),Sheet1!$G5,Sheet1!$E5-VLOOKUP($B5,Sheet2!$A$6:$E$10,5,0)+Sheet1!$G5)

I'm not sure that gak has it completely correct. Vlookup is the correct function to use here to find the matching value in sheet 2, as gak has pointed out.

If I'm following your logic correctly this is the formula I get:

=IF($G5=VLOOKUP($B5,Sheet2!$A$6:$H$10,5), VLOOKUP($B5,Sheet2!$A$6:$H$10,7), $G5-VLOOKUP(B5,Sheet2!$A$6:$H$10,5)+VLOOKUP($B5,Sheet2!$A$6:$H$10,5))

You said "Subtract the Sheet 2 Col E from Sheet 1 Col E (Market Rent)" but market rent is column G of sheet 1 so this is what my formula uses.

Originally Posted by Alex H

You said "Subtract the Sheet 2 Col E from Sheet 1 Col E (Market Rent)" but market rent is column G of sheet 1 so this is what my formula uses.

Good Call Alex, it should have been Col G. That said your formula still isn't returning what I am looking for. It seems to return the market rent in most cases? For Example Row 8 Should return 704 but instead I'm getting 774?

Thanks so much for taking the time to help here!

Yes, Alex is right, I got my Sheet1 and Sheet2 mixed up.

Maybe it would help if I explained my example.

Row 8 The market rent on Sheet 1 is 774 and the market rent on sheet 2 is 739 (both under unit type 1C10) Since they are different I take 774-739 and get 35. Add that to sheet 2 Col G (669) it should return 704.

Does that help? Thanks!

In Alex H's formula change the last 5 to a 7 and you'll have what you want.

That did it!

I promise to spend at least an hour tomorrow trying to understand the VLookUp Function, but I can thank you enough for saving me a ton of time!

No problem vlookup is a very powerful tool. Unfortunately because we have to lookup a few times in this example it's not that easy to understand if it's the first time you've come across it.

Try to start by thinking about just one chunk: VLOOKUP($B5,Sheet2!$A$6:$H$10,5) looks for B5 in the first column of the range Sheet 2 A6:H10, and returns the value from the 5th column of that range.

I'd also put a ,0 or ,FALSE at the end of the function. If this is omitted it will find the next closest alternative rather than return an error if it can't find the value you're looking up. It can make you think it's working when it's giving the wrong info.

Originally Posted by Alex H

No problem vlookup is a very powerful tool. Unfortunately because we have to lookup a few times in this example it's not that easy to understand if it's the first time you've come across it.

Try to start by thinking about just one chunk: VLOOKUP($B5,Sheet2!$A$6:$H$10,5) looks for B5 in the first column of the range Sheet 2 A6:H10, and returns the value from the 5th column of that range.

I sort of get it, but where in the formula does it tell it to look in the first column of that range? Or does is always default to the first column? Also isn't it returning the value from the 6th column of the range? Why not make the range just A6 to A10? Thanks!

It always looks in the first column of your range (that doesn't need to be column A), it's a default for the VLookup function. No it's the 5th column for Market rent and 7th column for reduced rent.

If you only selected 1 column (A6:A10) it would only be able to return the value you're looking up. That wouldn't be very useful as you already know it (unless you used it as a test).

I suggest you look up VLookup in Excel help for a better explanation.