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!

 

[Peter_SSs]

Thank you so much Peter! It Works!

That's good news, thanks for letting us know.

 

[John_82]

I have a similar issue to those listed in the thread, but slightly different. I would like to be able to find the proper value based on 2 inputs (matching the x and y coordinates basically - example below looking at A column and the row that has 5 in the column just to the left of A should return 16). If it's just one column and I don't need to have it figure out it needs to look in column with the A header, I can do that with a vlookup, but it's putting that together with which column to use that's confusing me right now. I'm using Excel 2010.

Thanks for any help you can provide!

Example:

Input 1 Input 2 Output A B C D E A 5 ? 1 2 23 4 19 9 should be 16 2 22 15 12 28 25 3 11 34 32 5 35 4 31 7 27 33 10 5 16 30 18 8 29 6 20 17 21 14 6 7 26 13 3 24 1 <tbody> </tbody>

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

 

[Ingolf]

You can use =index(rangetolook, match(....), match(...)
It depends on the location of the database.

 

[John_82]

Ah perfect, that does it. For whatever reason, I wasn't thinking of using 2 Match functions inside the Index.

Thanks!

 

[BrandonAS]

I would like to ask if there is one more criteria we could put on the function.
Starting with your table, we add a first column ID with unique numbers 1-4. Then in another table at F1:H4, what I would like to do is for each ID find the Header associated with the minimum value. I want it in a separate table because it has to be sortable, i.e. the ID order does not have to be sequential. In cell H2 I tried {=MATCH( G2, IF(F2=tbl_1[ID], tbl_1[[Header1]:[Header3]]) ,0)} to get the column number in tbl_1 where the minimum occurs, but it returns #N/A.

	A	B	C	D	E	F	G	H
1	ID	Header1	Header2	Header3		ID	Min	Location
2	1	22	10	65		3	31	formula 3
4	2	44	15	59		1	10	2
3	3	48	94	31		2	15	2
4	4	33	29	86		4	29	2
	tbl_1					tbl_2

<tbody>
</tbody>

Thanks in advance for your help.

 

[Aladin Akyurek]

@BrandonAS

G2, copied down:

=MIN(INDEX($B$2:$D$5,MATCH($F2,$A$2:$A$5,0),0))

H2, copied down:

=INDEX($B$1:$D$1,MATCH($G2,INDEX($B$2:$D$5,MATCH($F2,$A$2:$A$5,0),0),0))

 

[BrandonAS]

Thank you so much. Much appreciated. (I posted an original thread with this problem before finding and adding to this thread and XOR LX also came up with the result.) http://www.mrexcel.com/forum/excel-questions/828735-match-if-array-formula.html I can't tell you how much I appreciate you getting back to me with the solution and so quickly, too.

 

[Aladin Akyurek]

Thank you so much. Much appreciated. (I posted an original thread with this problem before finding and adding to this thread and XOR LX also came up with the result.) http://www.mrexcel.com/forum/excel-questions/828735-match-if-array-formula.html I can't tell you how much I appreciate you getting back to me with the solution and so quickly, too.

You are welcome. The problem does not require any array-processing formula. (Please do not post the same question multiple times. Thank you.)

 

[BrandonAS]

thank you. I didn't intend to post the question twice, I searched for an answer before posting, finding nothing I posted it. Later I searched for it using different key words and ran upon this post. Then I responded to the post with a follow on question. I'll be careful in the future. Thanks again.

 

[jericksonapga]

This thread appeared to be exactly what I was looking for, but all my attempts to get it to work on my spreadsheet return errors.

I have a table consisting of numbers and blank cells from G11:LL330. I want to find where a number,let's say "14," is located in this table and copy the contents of the cell in Row 1 of the column where 14 was found into cell A1. Then I want to copy the contents of the cell in Column 1 of the row where 14 was found into cell A2. It sure sounds like the same problem described above, but I'm missing something.

Oh, and I'm using EXCEL 2010