Hello, I am having the hardest time figuring this one out in excel can someone please help me out. I have three columns two with duplicate sorting information and the third the actual content I want. What function will enable me to pull the third column based off of the first two. See example attached. Thank you! Let me know if you have more questions about my issue.
Last edited by dwyngard; 06-30-2011 at 12:57 PM.
You'll need to insert a column in your lookup table and enter this formula dragged down
=A1&B1
Then whereever you are looking things up (in my example the Lookup sheet)
=VLOOKUP(A2&B2,Table!$C$1:$D$5,2,FALSE)
Does that work for you?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
Yes that is exactly what I needed. Thank you! I did have one other issue through that you may know the answer to. When there is a result that comes back false it says #N/A, how can I make it come back $0. Thank you.
Wrap the VLOOKUP formula in an IFERROR (Excel 2007) or IF(ISNA()) function, for example:
=IF(ISNA(VLOOKUP(A2&B2,Table!$C$1:$D$5,2,FALSE)),0,VLOOKUP(A2&B2,Table!$C$1:$D$5,2,FALSE))
or
=IFERROR(VLOOKUP(A2&B2,Table!$C$1:$D$5,2,FALSE),0)
Great thanks I used the IFERROR and it worked great!
How do I mark this thread as solved?
Last edited by dwyngard; 06-30-2011 at 09:09 AM.
Go to your first post, click on the edit button > go advanced and change the prefix. Glad we could help.
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
I actually ran into one more question. Thanks guys for helping me out this has been amazing. What I need to figure out is if there is duplicate information as displayed in the example, how do I SUM them in the VLOOUP formula.
You're going to have to move to SUMPRODUCT. Try this formula
=SUMPRODUCT(--(Table!$A$1:$A$6=A2),--(Table!$B$1:$B$6=B2),Table!$D$1:$D$6)
Here's a link to learn a little more about SUMPRODUCT
http://www.contextures.com/xlFunctio...tml#SumProduct
Does that work for you?
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks