LinkBack URL
About LinkBacks
If two cells contain specific text, return text from third cell. If one of the first two cells is not found, return zero.
Please refer to attached sample document.
Thank you.
If two cells contain specific text, return text from third cell. If one of the first two cells is not found, return zero.
Please refer to attached sample document.
Thank you.
Try this in B2:
=IFERROR(LOOKUP(2,1/($A2=Data!$A$2:$A$10)/(B$1=Data!$B$2:$B$10),Data!$C$2:$C$10),0)
In your data sheet insert a column in Column C and enter the following
then drag it down, you can then hide this column to keep everything looking the same.
=A2&B2
then in B2 of your Overview enter:
=IF(ISERROR(INDEX(Data!$C:$D,MATCH($A2&B$1,Data!$C:$C,0),2)),0,INDEX(Data!$C:$D,MATCH($A2&B$1,Data!$C:$C,0),2))
If someone has helped you then please add to their Reputation
However Phuocams version is far better
Phuocam, that worked. Now what if I want to lookup if one of the fields contains a portion of text? For example, if I have light blue as one of the colors but want to count that as blue?
I appreciate the assistance!Thank you.
You could use this array formula in B2, copied across and down:
=IFERROR(INDEX(Data!$C$2:$C$10,MATCH(1,(Overview!$A2=Data!$A$2:$A$10)*(ISNUMBER(SEARCH(B$1,Data!$B$2:$B$10))),0)),"")
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Glenn, that worked perfectly! This also helped my computer from freezing up. Thank you!
Glad to have helped.
You can try normal formula:
In B2 copied across and down.
=SUMPRODUCT((Data!$A$2:$A$10=$A2)*(ISNUMBER(SEARCH(B$1,Data!$B$2:$B$10)))*(Data!$C$2:$C$10))
Another way.Formula:
=INDEX(Overview!$B$2:$D$5,MATCH(Data!A2,Overview!$A$2:$A$5,0),MATCH("*"&Data!B2&"*",Overview!$B$1:$D$1,0))
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks