• 469K Members
• 3,659 Online
• 567K Conversations

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

Occasional Contributor

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

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

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

That could be

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

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

@Sergei Baklan Pi rocks!

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

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

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

The result returned is #N/A.

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

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.

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

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?

Highlighted

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

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.

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

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

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

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

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

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.

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

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.

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

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

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies