Index Match Issue with horizontal record

[mianmithu]

I am sorry to bother if its a simple question but i got a trouble doing this stuff

Here i got the Data like this

A	B	C	D
Pk	USA	UK	Aus
1	2	3	4
Ind	Tur	Can	Nep
5	6	7	8

I Want results (Name of Country) in E2 if i put the number (given digits) in E1
E.g. if i put number 7 in E1, The result in E2 must be Can.

P.s, I want Formula instead of VB Code.
I am trying Index match but its not giving me results but an error. Plz help

 

[KOKOSEK]

For ex. like that:

=INDEX(A1:D4,SUM(IF(A1:D4=E1,ROW(A1:D4)-1,0)),SUM(IF(A1:D4=E1,COLUMN(A1:D4),0)))

accept it with Ctrl+Shift+Enter (not just Enter as usual)

 

[jasonb75]

Or a regular formula

=INDEX($A$1:$D$4,INT((E1-1)/4)*2+1,MOD(E1-1,4)+1)

MATCH can only find a result in a single row or column, with multiple rows or columns you wil get an error.

 

[mianmithu]

Perfect, its an array now.
It worked

Thanks very much Kokosek God bless you

 

[KOKOSEK]

Indeed Jason. Anyhow in case of multiple matching data your formula 'could' give unexpected result.
@ mianmithu - please check Jason's post about multiple data and consider also his solution.

 

[mianmithu]

its only working in case i have data till column D, if i put more data in E, F... it will not work as it has nothing to do with the given number in A2 and A4, but it is counting the Cells from left to right till 4 and then again from A in the 2nd next row

Or a regular formula

=INDEX($A$1:$D$4,INT((E1-1)/4)*2+1,MOD(E1-1,4)+1)

MATCH can only find a result in a single row or column, with multiple rows or columns you wil get an error.

 

[jasonb75]

In that case, KOKOSEK's formula is also likely to fail, by using different values in rows 2 and 4, #REF! errors are not uncommon.

edit:- ignore that, the errors were caused by duplicates, anything else works fine.

 

[mianmithu]

In that case, KOKOSEK's formula is also likely to fail, by using different values in rows 2 and 4, #REF! errors are not uncommon.

No brother,

=INDEX(A1:D4,SUM(IF(A1:D4=E1,ROW(A1:D4)-1,0)),SUM(IF(A1:D4=E1,COLUMN(A1:D4),0)))
Change it into it, and it is no trouble.

=INDEX(A1:H8,SUM(IF(A1:H8=K1,ROW(A1:H8)-1,0)),SUM(IF(A1:H8=K1,COLUMN(A1:H8),0)))