help: lookup & References

  • Thread starter Thread starter Krappo
  • Start date Start date
K

Krappo

Im working on "INDEX" formula for cells B8 [=INDEX(A2:F6,C8,2)] and C9
[=INDEX(A2:F6,C8,1)].
And C8 as an Input which responsible for the changes in B8 and C9. The
problem is that I
don't know how to write a formula for AREA 2 in C10 (which is corresponding
to B8 and it's
return's value must be "10" as shown in AREA 2 Table).


AREA 1
1 Smith 11 11 34 32
2 Smith 11 11 30 32
3 Alan 20 41 26 71
4 Jane 144 55 68 85
5 Mary 12 55 33 47

Alan 3 <------This is the input value
AREA 1 = 3 <------The formula is =INDEX(A2:F6,C8,1)
AREA 2 = 10 <------need formula for Alan
*** And the formula for Alan in B8 is =INDEX(A2:F6,C8,2)
AREA 2
1 Susan 55 11 12 11
2 Jane 66 55 68 85
3 Lilo & Stitch 55 44 43 22
4 Mary 12 55 33 47
5 Smith 11 11 30 32
6 Smith 11 11 34 32
7 Mike 68 71 32 47
8 Mr.Brown 71 32 47 31
9 James 41 11 55 11
10 Alan 20 41 26 71
11 Harith 11 55 11 55


I've tried using formulas in "Lookup & Reference" Category but none of them
meets my need.

Thanks in advance those who willing to help me walk out of this problem.


Krappo.
 
Use the MATCH() function to find the position of Alan in your second list,
and put it into another INDEX() function. I'm assuming that AREA 2 is in
A12:F22.

=INDEX(A12:F22,MATCH(B8,B12:B22,0),1)

HTH
Steve D.
 
You can concatenate the values [include a delimiter such as CHAR(127)] from
each row into a single cell in an unused column, and use that for the lookup
instead of just the name.

Type

=$A2&CHAR(127)&$B2&CHAR(127)&$C2&CHAR(127)&$D2&CHAR(127)&$E2&CHAR(127)&$F2

into H2 and copy it down, then use the same formula as before, but refer to
column H where previously you refered to column B.




Krappo said:
Thanks Stephen,

Yes, I bet you've figured it out and your formula works just fine. But when
I input number "1" in C8;

Cells in B8 must equal to Smith
AREA 1 must equal to 1
AREA 2 must equal to 6 (but in this case it's equal to 5?)

The real looks is:-
In AREA 1
1 Smith 11 11 34 32

In AREA 2
6 Smith 11 11 34 32

Where the person named "Smith" are actually not the same person which are
shown as below:

In AREA 1
2 Smith 11 11 30 32

In AREA 2
5 Smith 11 11 30 32

Can we make another way?

My appreciations on your help.
Krappo.


Stephen Dunn said:
Use the MATCH() function to find the position of Alan in your second list,
and put it into another INDEX() function. I'm assuming that AREA 2 is in
A12:F22.

=INDEX(A12:F22,MATCH(B8,B12:B22,0),1)

HTH
Steve D.


"Krappo" <-> wrote in message news:[email protected]...
Im working on "INDEX" formula for cells B8 [=INDEX(A2:F6,C8,2)] and C9
[=INDEX(A2:F6,C8,1)].
And C8 as an Input which responsible for the changes in B8 and C9. The
problem is that I
don't know how to write a formula for AREA 2 in C10 (which is corresponding
to B8 and it's
return's value must be "10" as shown in AREA 2 Table).


AREA 1
1 Smith 11 11 34 32
2 Smith 11 11 30 32
3 Alan 20 41 26 71
4 Jane 144 55 68 85
5 Mary 12 55 33 47

Alan 3 <------This is the input value
AREA 1 = 3 <------The formula is =INDEX(A2:F6,C8,1)
AREA 2 = 10 <------need formula for Alan
*** And the formula for Alan in B8 is =INDEX(A2:F6,C8,2)
AREA 2
1 Susan 55 11 12 11
2 Jane 66 55 68 85
3 Lilo & Stitch 55 44 43 22
4 Mary 12 55 33 47
5 Smith 11 11 30 32
6 Smith 11 11 34 32
7 Mike 68 71 32 47
8 Mr.Brown 71 32 47 31
9 James 41 11 55 11
10 Alan 20 41 26 71
11 Harith 11 55 11 55


I've tried using formulas in "Lookup & Reference" Category but none of them
meets my need.

Thanks in advance those who willing to help me walk out of this problem.


Krappo.
Click to expand...
Click to expand...
Click to expand...
 
Stephen,

I knew it, there must be a way. That was a brilliant idea. I can use that
as a unique id.

Thanks a bunch! You are great.

Regard,
Krappo.

p.s. My appologize that I've emailed you by accidentally previously as I
thought I was reply to newsgroup.
 
Back
Top