Hello,
I am going to do my best to describe this issue but it might be bad. I have three columns of names I need to search to try and pull a number in from another sheet. However, I only know how to create an Index/Match (or VLOOKUP) to lookup one column at a time. I tried to mess around with it but i just got errors back. Please take a look at my attachment and let me know if you have more questions. Thanks for any help you can provide!
Try in Cell D2
=SUMPRODUCT((Sheet2!$A$2:$B$7=CONCAT($A2:$C2))*Sheet2!$C$2:$C$7)
Copy down
Life's a spreadsheet, Excel!
Say thanks, Click *
Why are the names in three columns? What is the logic behind Name1, Name2 and Name3?
Do you want to search for the name in both columns from the other sheet?
1. Click on the * Add Reputation if you think this helped you
2. Mark your thread as SOLVED when question is resolved
Modytrane
This did work in my example, let me take it to my real worksheet and i will get back to you!
This obviously is a watered down worksheet of what i am doing. Due to the way the data is set up in my actual worksheet, this is just the reality of it! it sucks but.. here we are lol.
I am getting a #VALUE error and I do not know why. I mean, nothing looks wrong with this right?
What are some errors that would lead to a #VALUE error? I've never used CONCAT with SUMPRODUCT before.
Please Login or Register to view this content.
I am not sure if CONCAT will work consistently in this case because of blanks in some cells.
Here's a simple formula [a bit long though] that will work.
I have attached copy of your original file with formulas in Cells D2 to D5.
You can adjust references to match your actual dataset.HTML Code:
Hello,
So this works a little bit! the issue i am having is if something in that row matches no names in the sheet 2, it just ends up copying whats on that line. So if line 1 (A3) is changed to "mmm" it will give back "1" because that's what is on line 1 on sheet 2.
Since my sheet 1 is longer than my sheet 2 on my real file, i get a bunch of #REF or #VALUE errors. I tried to add a "Not Assigned" option to sheet 2 because that is what is populated on my real sheet, but that just gives me a #REF error now.
Any ideas?
I have attached an example
Add IFERROR to the formula in D2 and copy down.
HTML Code:
Alright, so that got rid of the "errors". The main issue I am having now is if the name in the column on sheet 1 doesnt match anything in sheet 2, it just throws the digit that lines up with that line on sheet 2. Example:
A2, A3 and A4 do not have any names from the table on sheet 2. On sheet 2, the number in A3 is 1. Instead of the formula pulling in nothing/blank, it pulls in the 1, resulting in false data. Does this explain the situation well enough?
Please try to be accurate in your message.
You said quote: On sheet 2, the number in A3 is 1.
The last sample you sent has "jim" in A3 on sheet2.
You also said quote: A2, A3 and A4 do not have any names from the table on sheet 2
I don't know what this means. A2,A3,A4 on sheet1 are blank and on sheet2 they are tim,jim,timmy.
Sorry, my mistake. Forget everything else and let me try to simplify this one issue:
If bob is in C2, D2 will pull a 5 on sheet 1. Perfect. But if you replace bob with mm, it will pull a 1 into D2 on sheet 1 because that is what is in D2 on sheet 2. It should just pull nothing and be blank. Does this make sense?
Please Login or Register to view this content.
OK. I was able to replicate the error you mentioned [although your details were inaccurate].
Anyway, now the formula is getting a bit too long but it works.
It takes care of situation when all three cells on sheet1 are blank.
here it is:
HTML Code:
It also takes care of the last situation you mentioned [replacing bob with mm].
It's not pulling the correct data or blanking out the blanks, i have no idea why it's not working. It works in the sample which is exactly how the real one is set up. There's no reason for it not to work... SO confused... This is the code that's in my actual worksheet. Does anything look wrong?
Please Login or Register to view this content.
In the formula that modytrane wrote 3 values (A2, B2 and C2) are being matched to the names on sheet2. In the formula in post #16 only 2 values (E2 and G2) are being matched to the Terms sheet.
From the formula in post #16 it would appear that the range Names 1:Formula for number should occupy cells E1:H10 on sheet1.
It also appears that Sheet2, now called "Terms" should contain names in columns D:E and numbers in column F.
When I set up the Sample file to match that configuration, then modytrane's formula becomes:Formula:
Please Login or Register to view this content.
Note that the 7's will need to be changed to 2253's
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks