User not logged in - login - register
Home Calendar Books School Tool Photo Gallery Message Boards Users Statistics Advertise Site Info
go to bottom | |
 Message Boards » » Excel Help Page [1]  
wdprice3
Suspended
44925 Posts
user info
edit post

I need some type of match / find / look up, non-vba function. Cannot sort, or in any way manipulate the array of data.

I have Columns A - E with data. Column A contains unique values in each row. Rows in Columns B - E contain between 0 and 4 values from Column A (one value per cell). No value is repeated within Columns B - E.

I need to find a value in Columns B - E and return the corresponding value from Column A of that row. I must use Columns B - E for the search and need a 1 cell solution, per search function.


A B C D E
1 2
2 3 5
3 4 7 8 9
4
5 6
6
7
8
9


Examples:
Find 2, Return 1
Find 3, Return 2
Find 5, Return 2
Find 9, Return 3

[Edited on April 26, 2017 at 12:06 PM. Reason : .]

4/26/2017 12:06:05 PM

wdprice3
Suspended
44925 Posts
user info
edit post

Got it

={INDEX(A1:A9,MAX(IF(B1:E9=[i]Searched Value[\i],ROW(B1:E9)-ROW(A1)+1)))}

4/26/2017 12:35:49 PM

dtownral
All American
20072 Posts
user info
edit post

would vlookup not work for this?

4/26/2017 1:45:14 PM

wdprice3
Suspended
44925 Posts
user info
edit post

no

4/26/2017 3:24:12 PM

eleusis
All American
23820 Posts
user info
edit post

Quote :
"-ROW(A1)+1"


What is the point of this? ROW(A1) will always return 1, regardless of your input cell. you're subtracting and adding one unnecessarily.

4/26/2017 4:17:40 PM

aaronburro
Sup, B
51478 Posts
user info
edit post

^ This is an array formula. The "A1" is behaving differently because of that. It effectively refers to the first column on whatever row the function is evaluating.

[Edited on April 27, 2017 at 12:48 AM. Reason : ]

4/27/2017 12:41:47 AM

eleusis
All American
23820 Posts
user info
edit post

it's not though. I tested the code before writing that, and it works fine without it.

[Edited on April 27, 2017 at 1:08 PM. Reason : nevermind. it's needed if the cells aren't actually located in B1-E9. not an array issue though]

4/27/2017 1:05:31 PM

 Message Boards » Tech Talk » Excel Help Page [1]  
go to top | |
Admin Options : move topic | lock topic

© 2017 by The Wolf Web - All Rights Reserved.
The material located at this site is not endorsed, sponsored or provided by or on behalf of North Carolina State University.
Powered by CrazyWeb v2.37 - our disclaimer.