If Statements using multiple tabs

**scyllanbay** · 11-30-2012, 05:30 PM

I'm drawing a blank today on how to create a formula to retrieve back the information that I need. I am going to try to explain this the best that I can.

Sheet 1 in Column A I have Lease #'s (ranging from 119.1, 122.1, 125.1, 125.5, etc)
I need a formula to look at Sheet 1 Column A and find it's same Lease # on Sheet 2 in Column A

then

I need it to look at Sheet 1 Cell AX3 and find it's match on sheet two in column B for the same Lease # that it has already matched together in step 1 above.

then

Once is has found both sets of pairs I need it to return the value from Sheet 2 in column M.

In other words I'm trying to say....If Sheet 1 A = Sheet 2 A and Sheet 1 B = Sheet 2 B then Sheet 2 C

Is that possible and does that make sense?

Thanks! Jennifer

**djapigo** · 11-30-2012, 06:22 PM

Hi Jennifer,

Is this what you have in mind?

**scyllanbay** · 11-30-2012, 06:35 PM

Does this spreadsheet better explain what I'm hoping to do?

Thanks so much!

**djapigo** · 11-30-2012, 06:47 PM

Hi Jennifer,

This is the simplest solution...

I used a helper column in Sheet 2 to concatenate column A and B, then used the VLOOKUP with concatenated lookup value.

You can hide the helper column if needed.

Is this ok?

**scyllanbay** · 11-30-2012, 06:56 PM

Wow, I sure can tell it's Friday. Your fix is perfect! I wasn't thinking at all when I looked at it.

Again, Thank you for the help!

**scyllanbay** · 11-30-2012, 06:53 PM

Hi.

Thank you. I might be able to make that work. However, I'm not sure if I"m doing something wrong but I took the formula that you created and copied it down the rest of the cells and i'm just getting a #N/A.

**djapigo** · 11-30-2012, 06:55 PM

Hi Jennifer,

Your sheet2 did not contain the other items... you can modify the formula to this to avoid the #N/As

=IFERROR(VLOOKUP($A4&B$2,Sheet2!$C$3:$D$10,2,0),"")

**djapigo** · 11-30-2012, 06:57 PM

No problem... TGIF!

**FDibbins** · 11-30-2012, 07:26 PM

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.