Finding a value in a multiple column array and returning column header - Excel 2003

[NairbNam]

Hi guys first post here.

I am attempting to lookup a cell value in a 3 column by 20 row array. Depending on which column the value is found, I would like to return that column's header value.

I think I am making this overly complicated for myself. Basically I need a statement that will perform an Hlookup on three columns but I'm not sure exactly how to do this.

Any help would be appreciated.

Thanks!

 

[Aladin Akyurek]

Sorry, it is not complet...
I haave changed tje COMULMN funtion by the ROW funtion, changed the cells identification and it did not work... This is the data matrix for this question:

A	B	C	D	E	F	G
CREA	10	11	21	31	41	60
ACAB	20	30	40	50	52	53
VGAM	20S	30S	40S	50S	52S	53S

<tbody>
</tbody>

Want I whant is giving the 50S, receive the VGAM result...
Thank you
HelderOliveira

Row\Col	A​	B​	C​	D​	E​	F​	G​	K​
1​	CREA	10​	11​	50S	31​	41​	60​	50S
2​	ACAB	20​	30​	40​	50​	52​	53​	2​
3​	VGAM	20S	50S	40S	50S	52S	53S	CREA
4​								VGAM
5​
6​

In K2 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF($B$1:$G$3=K$1,ROW($A$1:$A$3)-ROW($A$3)+1),ROW($A$1:$A$3)-ROW($A$3)+1),1))

In K3 control+shift+enter, not just enter, and copy down:

=IF(ROWS($K$3:K3)>K$2,"",INDEX($A$1:$A$3,SMALL(IF($B$1:$G$3=K$1,ROW($A$1:$A$3)-ROW($A$1)+1),ROWS($K$3:K3))))

 

[NemoS]

Hello. I'm trying to do something similar only without the ranges being next to each other. Here's an example

Row/Col	A	B	C	D	E	F
1	RangeA		RangeB		LookingFor	FoundIn
2	aa		ff		cc	RangeA
3	bb		gg		ii	RangeB
4	cc		hh		aa	RangeA
5	dd		ii

</br> Is this possible?

 

[Aladin Akyurek]

@ NemoS

Is there any reason why the following would not work?

In F2 control+shift+enter, not just enter, and copy down:

=INDEX($A$1:$C$1,MIN(IF($A$2:$C$5=$E2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)))

 

[NemoS]

@ NemoS Is there any reason why the following would not work? In F2 control+shift+enter, not just enter, and copy down: =INDEX($A$1:$C$1,MIN(IF($A$2:$C$5=$E2,COLUMN($A$1:$C$1)-COLUMN($A$1)+1)))

It works as long as each range header is on the same row, which they wont always be. For example:

Row/Col	A	B	C	D	E	F
1	RangeA		RangeB		LookingFor	FoundIn
2	aa		ff		cc	RangeA
3	bb		gg		ii	RangeB
4	cc		hh		aa	RangeA
5	dd		ii		kk	RangeC
6					oo	RangeD
7	RangeC		RangeD
8	jj		oo
9	kk
10	ll		qq
11			rr

Then you can imagine ranges E and F will start with headers in row 13 and contain up to 4 strings under each as well. Each range will always be the same size, though some entries may be empty within that range. There will also be other data in-between, like columns B and D, though that data will never contain any strings that would be found.

 

[NemoS]

Never-mind! I have found a solution! Posting here for anyone else who wants it: =ArrayFormula(if(len(E2:E6), vlookup(E2:E6, split({A2:A5&"_"&A1; C2:C5&"_"&C1; A8:A11&"_"&A7; C8:C11&"_"&C7}, "_"), 2, 0),)) This was done in google sheets so I'm not sure if it'll work the same in MS excel. Usually if it works in one it'll work in both though...

 

[Aladin Akyurek]

Never-mind! I have found a solution! Posting here for anyone else who wants it: =ArrayFormula(if(len(E2:E6), vlookup(E2:E6, split({A2:A5&"_"&A1; C2:C5&"_"&C1; A8:A11&"_"&A7; C8:C11&"_"&C7}, "_"), 2, 0),)) This was done in google sheets so I'm not sure if it'll work the same in MS excel. Usually if it works in one it'll work in both though...

1. Your initial sample and the later sample are quite different qua data representation they involve.

2. Excel does not include a 'split' function, so the formula has not much to offer to an Excel user.

3. Try the following if you would need Excel...

Row\Col	A​	B​	C​	D​	E​	F​
1​	RangeA		RangeB		LookingFor	FoundIn
2​	aa		ff		cc	RangeA
3​	bb		gg		ii	RangeB
4​	cc		hh		aa	RangeA
5​	dd		ii		kk	RangeC
6​					oo	RangeD
7​	RangeC		RangeD
8​	jj		oo
9​	kk
10​	ll		qq
11​			rr

In F2 just enter and copy down:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3,4},IF(MATCH(E2,$A$2:$A$5,0),$A$1),IF(MATCH(E2,$C$2:$C$5,0),$C$1),IF(MATCH(E2,$A$8:$A$11,0),$A$7),IF(MATCH(E2,$C$8:$C$11,0),$C$7)))

 

[jenny1013]

I'm trying to do something similar though my columns each have a different number of items in them.

Column "type of" is my expected result. I'm looking at the items in the column "type" and then wanting to find the header (Single Family, Multi Family, or Non-Res") Note - The number column isn't something that needs to be matched in this. Thank you!

Number	Type		Type of		Single Family	Multi Family	Non-Res
1	House		Single Family		House	Condo	Park
2	Condo		Multi Family			Apartment	Store
3	Apartment		Multi Family				Warehouse
4	Park		Non-Res				Restaurant

<colgroup><col span="3"><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Aladin Akyurek]

@ jenny1013

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​	Number	Type	Type of		Single Family	Multi Family	Non-Res
2​	1​	House	Single Family		House	Condo	Park
3​	2​	Condo	Multi Family			Apartment	Store
4​	3​	Apartment	Multi Family				Warehouse
5​	4​	Park	Non-Res				Restaurant

<tbody>
</tbody>

In C2 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS($E$2:$G$5,$B2),INDEX($E$1:$G$1,MIN(IF($E$2:$G$5=$B2,COLUMN($E$1:$G$1)-COLUMN($E$1)+1))),"not found")

 

[Aladin Akyurek]

Please replace COUNTIFS with COUNTIF for your version of Excel.

 

[Jaywatch]

Hi

This is my first post so a little nervous.

I have a situation very similar to the one shown here but occasionally there are multiple repeated values.

I thought i'd got around this by using LARGE instead of MAX in the equation. I have the value of 'k' outside the equation in a convenient cell where I can change it at will.

I'd better mention that I have the equation calculating the ROW header as well as the COLUMN header...

Using the example above I have

The columns B,C and D instead of Header_1, Header_2 etc.
And I am using the line numbers 40 to 43. I am using cell E39 as my 'k' value. Hence

{=INDEX(B39:D39,LARGE(IF(B40:D43=E40,COLUMN(B40:D43)-COLUMN(B39)+1),E39))} For the row number and

{=INDEX(A40:A43,LARGE(IF(B40:D43=E40,ROW(B40:D43)-ROW(A40)+1),E39))} for the column letter.

So here's the thing... Sometimes it works and sometimes it doesn't.

If I use the original formula (i.e. MAX) when there are ... say three values of 48; the solution will give the position of the third value.
and using the above equation I get the same result when 'k' is set to 1. No problem so far.

However, if one of the duplicate values happens to be on the same row or column the COLUMN value might be right but sometimes the ROW is not. By changing E39 to 2 or 3 (i.e. the 'k' value) it will only give the correct cell (via the two equations above) if all three are on different rows and different columns.

All the above was used to see if it would work on my real database. I wasn't convinced... So I went ahead and did it anyway.
Sometimes this is what is happening.

If the real solutions are H4, M3 and V4 (to all intents and purposes these are cell addresses)
The answers I am getting are... H3, M4 and V4. (The first pair column H and row 4 are both using a 'k' value of 3 which is correct; yet the result is incorrect. And this is so annoying!

Has anyone any ideas about the equations? Is there something I am missing?