I have the following formula: {=IFERROR((INDEX(Details!$I$2:$I$3000,(MATCH(1,(Details!$B$2:$B$3000=A226)*(Details!$A$2:$A$3000="234411")*(Details!$D$2:$D$3000="b"),0)))),"")}
I need the phrase (Details!$D$2:$D$3000="b") to include "a" or "b". Is that possible?
Maybe try change this Details!$D$2:$D$3000="b" to Details!$D$2:$D$3000={"a","b"}
I had tried that with no success.
Can you post your workbook?
I'll give it a try.
Try this one instead..........
What are you doing with Column D in detail worksheet? I am a little bit lost on your lookup criteria. But I can see it is definitely doable.
If you look in cells G226 and H226 on the Analysis tab, you will find the formulas I'm working with.
I would like the values in col G and Col H to fill in only if the value in col D on the Details tab is A or B.
If it is ship to 23411 in Column A (details), what do you want in RIM status 234065 on analysis?
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.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I need it to match the part number and the branch number (234065 or 234411) from the Analysis tab to the
Details tab, and return the RIM status, but only when the Freq Class Code on the Details tab is "A" or "B".
Try this. I am still not very clear on the criteria, but I guess I will take a short.
Enter the formula with Ctrl + Shift + Enter keys together since it is an array formula
=IFERROR(INDEX(Details!$I$2:$I$2617,IF(TRIM(LEN(B4&C4))=0,"",IF(B4<>"",MATCH(G$1&$A4&"A",Details!$A$2:$A$2617&Details!$B$2:$B$2617&Details!$D$2:$D$2617,0),MATCH(G$1&$A4&"B",Details!$A$2:$A$2617&Details!$B$2:$B$2617&Details!$D$2:$D$2617,0)))),"")
Hi. I didn' quite understand your answer at post 10. So... Here's my punt at an answer (Version 1, anyway...). Wherever you're getting this from, it is storing the numbers as text. That's a pain. So (I hope that is is possible in the real sheet) I "bulk converted" everything to a number. I then cut down your data to something more manageable (for testing purposes).
Does this look like what you want. It doesnt find the 3 entries (in gold shading) on the detailas sheet, where col D is neither A nor B. It didn't make sense (to me) why return should be made in BOTH the RIM columns, if only ONE applied. So, I've meddled with that, too.
So. It'll not be what you want (probably... yet). But maybe it's a start.
I apologize for not being able to explain my problem more clearly. I believe I solved it with a Sumproduct formula.
=IF(SUMPRODUCT((Details!$A$2:$A$3000="234065")*(Details!$B$2:$B$3000=A4)*(Details!$D$2:$D$3000={"a","b"})*(Details!$I$2:$I$3000))>0,"0"&SUMPRODUCT((Details!$A$2:$A$3000="234065")*(Details!$B$2:$B$3000=A4)*(Details!$D$2:$D$3000={"a","b"})*(Details!$I$2:$I$3000)),"")
Thank you for all your efforts!!!
Glenn,
Your array formula worked also. As so often is the case, there's more than one way to skin an excel cat.
Thanks again.......
Dave
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks