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!

 

[moxiegretl]

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

That might have been why other formulas weren't working, but it isn't affecting the formula I have in there - at least not the examples I gave. 53208 and 53149 were not duplicates but are still pulling the wrong region. I corrected the duplicates and that did not change anything.

 

[merlin777]

Can you check they are all numbers rather than text which would evaluate incorrectly? It's an outside chance but worth a look.

I know hlookup and vlookup are sensitive to being pre sorted and probably not pertinent to this but its worth a look too.

 

[moxiegretl]

Can you check they are all numbers rather than text which would evaluate incorrectly? It's an outside chance but worth a look.

I know hlookup and vlookup are sensitive to being pre sorted and probably not pertinent to this but its worth a look too.

That's why I'm not using hlookup or vlookup plus I need the formula to check the entire table, not just a single column. I double-checked and they are numbers, not text. Thank you for helping troubleshoot, it's nice to have another set of eyes on this!

 

[Rick Rothstein]

=INDEX(Regions!$A$1:$K$1,MAX(IF(Regions!$A48:K$250=June!M64,COLUMN(Regions!$A$2:$K$250)-COLUMN(Regions!A47)+1)))

It looks like your range are mismatched in size, that you are applying the column offset for the INDEX function to its Row argument and that you have an extra calculation that cancels out to 0. See if this formula works for you...

=INDEX(Regions!$A$1:$K$1,,MAX(IF(Regions!$A2:K$250=June!M64,COLUMN(Regions!$A$2:$K$250))))

 

[Aladin Akyurek]

@moxiegretl

Thanks for the sample.

Let A1:G21 of Regions house this sample.

Let A2:A3 of Sheet1 house the zipcodes 53208 and 53149.

In B2 of Sheet1 control+shift+enter, not just enter, and copy down:

=IF(COUNTIFS(Regions!$A$2:$G$21,$A2),INDEX(Regions!$A$1:$G$1,MIN(IF(Regions!$A$2:$G$21=$A2,COLUMN(Regions!$A$2:$G$21)-COLUMN(Regions!$A$2)+1))),"zipcode not found")

 

[moxiegretl]

Aladin - IT WORKS!!!!!!! I am forever grateful! This is an incredibly helpful group/thread/website!

Rick - I tried your formula suggestion too and it seemed to work for the first few cells, but further down it pulled only Region 1 and stopped pulling the correct header. Thank you anyway!

 

[Aladin Akyurek]

@moxiegretl

You are welcome.

 

[HelderOliveira]

Good afternoon,
My problem is quit the same but I whant the row row value insted of the column one. Is it possible? I have changed the COLUMN

 

[HelderOliveira]

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

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

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

 

[Peter_SSs]

See if this suits you.

Book1
	A	B	C	D	E	F	G	H	I	J
1	CREA	10	11	21	31	41	60		50S	VGAM
2	ACAB	20	30	40	50	52	53
3	VGAM	20S	30S	40S	50S	52S	53S
Row Label

Cell Formulas
Range	Formula
J1	=INDEX($A$1:$A$3,AGGREGATE(15,6,(ROW($A$1:$A$3)-ROW($A$1)+1)/($B$1:$G$3=I1),1))