Look up values in 3 successive columns and return the value in final row from 4th column.

Copper Contributor

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.

 

12 Replies

That could be

=LOOKUP(PI(),1/((A1:A200=14)*(B1:B200="RB1")*(C1:C200=1)),D1:D200)

@Detlef Lewin , I was going to use 2 as usual, but recall your discussion here long ago about the PI(). Just more sexy...

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.

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?

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.

Not sure how to upload to this site.  Hopefully, attached to this post is a sample with examples of what I have tried.

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

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.

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

image.png

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.

Thanks for the assist.  I have it working.  Great suggestions!