Feb 10 2019 09:22 AM
I have data in columns that I need to look up a value in the first column, then the second, then the third and return the value found in the final row and a fourth column.
Example: Within the rows that column A=14, and column B="RB1", and column C=1, return the value in column D.
Feb 10 2019 10:25 AM
That could be
=LOOKUP(PI(),1/((A1:A200=14)*(B1:B200="RB1")*(C1:C200=1)),D1:D200)
Feb 10 2019 10:33 AM
@Sergei Baklan Pi rocks!
Feb 10 2019 10:41 AM
@Detlef Lewin , I was going to use 2 as usual, but recall your discussion here long ago about the PI(). Just more sexy...
Feb 10 2019 10:43 AM
The result returned is #N/A.
Feb 10 2019 11:00 AM
It returns #N/A if you have no such combination. For such cases you may wrap formula like
=IFNA(LOOKUP(PI(),1/((A1:A200=14)*(B1:B200="RB1")*(C1:C200=1)),D1:D200),"no such")
In general it works, please check attached file.
Possible reasons why doesn't match - you compare text "1" and number 1; etc.
Feb 16 2019 11:57 AM
Still having trouble with this process.
Column A is values
Column B is text
Column C is values
Colum D is value
I need to find a value in column A (there will be from 5 to 20), within these rows find in column B a certain label (may be 1 or 3 choices), within these 3 choices in column C a value and return the value listed in that row from column D.
Your attached file is close to what I have but the formula still doesn't work.
Any other suggestions?
Feb 16 2019 01:42 PM
Format doesn't matter, with the rest I don't catch what's wrong. Could you please provide small sample file or modify attached one to illustrate what doesn't work.
Feb 18 2019 10:20 AM
Not sure how to upload to this site. Hopefully, attached to this post is a sample with examples of what I have tried.
Feb 18 2019 01:06 PM
1) You start formulas from row #5, actually your range starts from #2
2) In column B your texts have leading space (" C", " RB1"), you don't take that into account and compare with "C", "RB1", etc
Please check attached fille
Feb 19 2019 08:54 AM
Thanks. I can see where the space in column B was a problem. The change from row 5 to 2 was just due to creating the example, the actual report always starts on row 5 due to headings. The next problem is that the formula is too fragile. If I change anything, an error results and I can't revert back. I need this to expand for about 75 different results. My goal was to have a template of sorts that I can drop the columns of data into (or copy the template into the report) and have Excel do the first round of evaluations by returning only the necessary data.
Feb 21 2019 01:20 PM
Yes, that's just the basic formula. After that you may modify it depends on how your real data is structured and how you'd like to present the result. I'm not sure what do you mean under the template. As an example you may select your values to lookup from drop-down lists as
formula here will be modified as
=IFNA(LOOKUP(PI(),1/(A5:A453=$K$5)/(B5:B453=" "&$L$5)/(C5:C453=$M$5),D5:D453),"no such")
data for drop-down list could be added manually or generated automatically from you source data. In attached files is quite simple illustration of such approach.
But basis formula is the same.
Mar 02 2019 11:20 AM
Thanks for the assist. I have it working. Great suggestions!