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]

Can someone please help me with this. I have a table as below. I would like to look up a number (say 3454) and return the number in the 1st column. (2)

A B C D Lookup Answer 1 1234 43556 3435 2 2 5678 3435 3 90123 4 74547 234 234 <tbody> </tbody>

<tbody>
</tbody>

Row\Col	A​	B​	C​	D​	E​	G​	H​	I​	J​	K​
1​		A	B	C	D	Lookup	Occurrence Count	Answer
2​	1​	1234​	43556​			3435​	2​	2​	8​
3​	2​	5678​			3435​	234​	1​	4​
4​	3​		90123​
5​	4​	74547​	234​	234​
6​	8​	3435​

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

=SUM(IF(FREQUENCY(IF($B$2:$E$6=$G2,ROW($A$2:$A$6)-ROW($A$2)+1),
    ROW($A$2:$A$6)-ROW($A$2)+1),1))

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

=IF(COLUMNS($I2:I2)<=$H2,INDEX($A$2:$A$6,SMALL(IF($B$2:$E$6=$G2,
    ROW($A$2:$A$6)-ROW($A$2)+1),COLUMNS($I2:I2))),"")

 

[Ptgalve]

Thank you so much Peter! It Works!

Hi Peter,

Ablebits recommended MrExcel and I found this post very helpful. It's my first time here and would like to seek your help with my query.

I'm building a scorecard with sheets formatted as follows:



Sheet1 (Actual Scores) / Sheet 2 (Monthly Goals)

Roster Group	Quality Score	Attendance	CSAT %	FDR%	FRR%	IB AHT	IB AHT (Sec)	IB Abandon %	Handle Time	SocMed Mark as Read	SocMed AWT (Hrs)	Orders Processed	Chats Received	Open Tickets	Solved Tickets	Ext-PingPong-All	Int-PingPong-All	Ext-PingPong-SWAT	Int-PingPong-SWAT
Digital	0.00%	100.00%	68.75%	92.11%	15.94%	0.12		0.00%	7.34	0			0	0	10	83.13%	98.08%	0.00%	0.00%
Digital	70.25%	95.73%	83.33%	62.82%	46.30%	0.11	5.58	0.00%	8.40	0			0	0	11	84.92%	94.54%	0.00%	0.00%
OP	85.00%	100.00%	75.00%	73.23%	80.21%	0.25		0.00%	14.77	0		269.50	0	2	5	97.22%	100.00%	0.00%	0.00%
GOALS
Month	Group	Metrics	Goal ID	0%	15%	30%	45%	50%	65%	75%	85%	90%	95%	98%	100%	Weight	Target
March	Digital	Attendance	MarchDigitalAttendance	70%	75%	80%	89%	90%	92%	94%	95%	96%	97%	98%	100%	15.0%	90%
March	Digital	Quality	MarchDigitalQuality	50%	60%	65%	84%	85%	88%	90%	92%	94%	95%	98%	100%	10.0%	85%
March	Digital	CSAT	MarchDigitalCSAT	50%	60%	65%	72%	75%	78%	81%	84%	87%	90%	95%	100%	20.0%	75%
March	Digital	Solved Tickets	MarchDigitalSolved Tickets	5	7	45	74	75	80	-	-	85	-	-	90	20.0%	75
March	Digital	Int-PingPong-SWAT	MarchDigitalInt-PingPong-SWAT	70%	75%	80%	89%	90%	92%	94%	95%	96%	97%	98%	100%	10.0%	90%
March	Digital	Ext-PingPong-SWAT	MarchDigitalExt-PingPong-SWAT	70%	75%	80%	89%	90%	92%	94%	95%	96%	97%	98%	100%	15.0%	90%
March	Digital	Tickets assigned >24 hours	MarchDigitalTickets assigned >24 hours	9	8	7	6	5	4	3	2	1	-	-	0	10.0%	5

<tbody>
</tbody>

Say I have Digital with Attendance of 95.73%, it will return 85% (based on goal header 0-100%) , multiplied by weight of 15%.

Not sure if this is how I should set this one up though. any advice would surely be appreciated.

Thanks,
Paul

 

[FatManCam]

Hi there,

Thanks for the solution here as it really helped me at work. Out of interest though, I typed in a lookup number in Cell E2 and that isn't in the table (e.g. 1000) and it comes back with the result of 'Header 1'. From evaluating the formula it comes up with FALSE for all A2:C5=E2, which turns the IF FALSE, then the MAX is FALSE which in turn is a zero. When the INDEX looks up 0, it returns Header 1.

Any ideas on how to insert an alert into the formula to let you know if the lookup figure isn't in the table of figures?

Thanks!

 

[Peter_SSs]

Hi there, ..

Welcome to the MrExcel board!

There are 52 posts in the thread before yours. It would be sensible to indicate which one you are referring to.

In general though

=IF(COUNTIF(RangeOfInterest,E2),ExistingFormula,"Not found")

 

[moxiegretl]

This is the only formula that is working for my spreadsheet, hooray! (I've been trying various solutions for the past 5 hours.) However, I'm having an issue with it pulling the EXACT value's column header. (My data consists of zip codes and the headers are different regions.) It is pulling mostly the first column, even if the corresponding zipcode is actually in another column. Help!

Try this...

Sheet1

	A	B	C	D	E	F
1	Header1	Header2	Header3	_	Lookup	Location
2	22	10	65	_	94	Header2
3	44	15	59	_	_	_
4	48	94	31	_	_	_
5	33	29	86	_	_	_

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:72px;"><col style="width:72px;"><col style="width:72px;"><col style="width:20px;"><col style="width:72px;"><col style="width:72px;"></colgroup><tbody>
</tbody>

This array formula** entered in F2:

=INDEX(A1:C1,MAX(IF(A2:C5=E2,COLUMN(A2:C5)-COLUMN(A1)+1)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Aladin Akyurek]

This is the only formula that is working for my spreadsheet, hooray! (I've been trying various solutions for the past 5 hours.) However, I'm having an issue with it pulling the EXACT value's column header. (My data consists of zip codes and the headers are different regions.) It is pulling mostly the first column, even if the corresponding zipcode is actually in another column. Help!

Care to post a small sample along with expected output?

 

[moxiegretl]

Care to post a small sample along with expected output?

I'd love to! So a sheet called Regions holds the Zipcode Region Table:

Region 1	Region 2	Region 3	Region 4	Region 5	Region 6	Region 7
53007	53005	53018	53001	53201	60804	53103
53017	53045	53029	53004	53202	60714	53105
53022	53122	53058	53010	53203	60712	53115
53027	53146	53066	53011	53204	60707	53119
53033	53151	53069	53011	53205	60706	53120
53037	53208	53072	53012	53206	60706	53120
53046	53210	53118	53013	53209	60666	53121
53051	53213	53127	53014	53211	60661	53128
53076	53214	53183	53014	53212	53104	53139
53086	53215	53186	53015	53217	53108	53147
53089	53222	53188	53015	53223	53110	53147
53095	53226	53189	53019	53224	53126	53149
53216	53227	53538	53019	53233	53129	53150
53218	53233	53598	53020		53130	53150
53225		54626	53021		53132	53153
53956			53021		53140	53156
53006			53023		53142	53157
			53024		53143	53168
			53040		53144	53185
			53040		53154	53190

<tbody>
</tbody>

The table is much larger than this, as several of the columns have more zipcodes/data.

Other sheets (named by months) contain names of customers, their info and when I type in their zipcode, I have a cell next to it with the formula that I want it to pull what region the zipcode belongs to. Here is the formula that I created based on the original thread that works for some, but not all cells: =INDEX(Regions!$A$1:$K$1,MAX(IF(Regions!$A48:K$250=June!M64,COLUMN(Regions!$A$2:$K$250)-COLUMN(Regions!A47)+1)))

I used the ctrl/shift/enter for the formula.

So for example, zipcode 53208 is in Region 2 and should pull that header, but it doesn't, it pulls Region 1. Another one is 53149 which is in Region 6, but it also pulls Region 1. This is happening a lot, pulling some correct headings, some incorrect headings. I think I know why it is happening but I cannot figure out how to fix it. Your help is most appreciated!

 

[merlin777]

Just checking - you know there are a lot of duplicates in your table?

 

[moxiegretl]

Just checking - you know there are a lot of duplicates in your table?

There shouldn't be, but I see that - working with the person who created the table to correct. Thank you!

 

[merlin777]

I'm not an excel whizzkid but it might be duplicates that are upsetting your results.