help with matching if

  • Thread starter Thread starter augustus
  • Start date Start date
A

augustus

The formula worked very well.
As you can see I am totally a noob to excel. And I have another noo
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to exce
to return the matching values from the table to a2.

for example:
[ ]|Y1 |Y2| Y3|
X1|.54|.65|.67|
X2|.56|.67|.89|

cell A1=Y1
cell A2=X1
A4 will return the matching values from y1,x1=.54

Thx for the help everyone.
Highly Appreciated
 
Say your lookup table is in C1:F3
Enter in A4:
=INDEX(D2:F3,MATCH(A1,D1:F1,0),MATCH(A2,C2:C3))

HTH
 
THX Aruds for your reply.
I'm not quite understand.
Can you please explain your formula.
Index D2 to F3 is the table, and match A1 with the D1 to F1 then A2
with c2to C3. but what if I want to want to excel to find both and
diplay it in the same cell?
Will the following formula work ?
=INDEX(A1:J10,MATCH(A1,A2,A1:J10,0))

Ardus said:
Say your lookup table is in C1:F3
Enter in A4:
=INDEX(D2:F3,MATCH(A1,D1:F1,0),MATCH(A2,C2:C3))

HTH
--
AP

"augustus" <[email protected]> a
écrit
dans le message de (e-mail address removed)...
The formula worked very well.
As you can see I am totally a noob to excel. And I have another noob
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to excel
to return the matching values from the table to a2.

for example:
[ ]|Y1 |Y2| Y3|
X1|.54|.65|.67|
X2|.56|.67|.89|

cell A1=Y1
cell A2=X1
A4 will return the matching values from y1,x1=.54

Thx for the help everyone.
Highly Appreciated.
Click to expand...
------------------------------------------------------------------------
Click to expand...
Click to expand...
 
THX Aruds for your reply.
I'm not quite understand.
Can you please explain your formula.
Index D2 to F3 is the table, and match A1 with the D1 to F1 then A2
with c2to C3. but what if I want to want to excel to find both and
diplay it in the same cell?
Will the following formula work ?
=INDEX(A1:J10,MATCH(A1,A2,A1:J10,0))

Ardus said:
Say your lookup table is in C1:F3
Enter in A4:
=INDEX(D2:F3,MATCH(A1,D1:F1,0),MATCH(A2,C2:C3))

HTH
--
AP

"augustus" <[email protected]> a
écrit
dans le message de (e-mail address removed)...
The formula worked very well.
As you can see I am totally a noob to excel. And I have another noob
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to excel
to return the matching values from the table to a2.

for example:
[ ]|Y1 |Y2| Y3|
X1|.54|.65|.67|
X2|.56|.67|.89|

cell A1=Y1
cell A2=X1
A4 will return the matching values from y1,x1=.54

Thx for the help everyone.
Highly Appreciated.
Click to expand...
------------------------------------------------------------------------
Click to expand...
Click to expand...
 
Try...

=INDEX(a1:F99,MATCH(x1,a1:a99,0),match(y1,a1:f1,0))


=index(somerange,match(somecell,firstcolofthatrange,0),
match(someothercell,firstrowofthatrange))

that will result in something that looks more like:
=index(somerange,row#,column#)


THX Aruds for your reply.
I'm not quite understand.
Can you please explain your formula.
Index D2 to F3 is the table, and match A1 with the D1 to F1 then A2
with c2to C3. but what if I want to want to excel to find both and
diplay it in the same cell?
Will the following formula work ?
=INDEX(A1:J10,MATCH(A1,A2,A1:J10,0))

Ardus said:
Say your lookup table is in C1:F3
Enter in A4:
=INDEX(D2:F3,MATCH(A1,D1:F1,0),MATCH(A2,C2:C3))

HTH
--
AP

"augustus" <[email protected]> a
écrit
dans le message de (e-mail address removed)...
The formula worked very well.
As you can see I am totally a noob to excel. And I have another noob
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to excel
to return the matching values from the table to a2.

for example:
[ ]|Y1 |Y2| Y3|
X1|.54|.65|.67|
X2|.56|.67|.89|

cell A1=Y1
cell A2=X1
A4 will return the matching values from y1,x1=.54

Thx for the help everyone.
Highly Appreciated.
Click to expand...
------------------------------------------------------------------------
Click to expand...
Click to expand...
Click to expand...
 
I dropped a 0:

=index(somerange,match(somecell,firstcolofthatrange,0),
match(someothercell,firstrowofthatrange,0))

The 0 tells =match() to find an exact match.

Dave said:
Try...

=INDEX(a1:F99,MATCH(x1,a1:a99,0),match(y1,a1:f1,0))

=index(somerange,match(somecell,firstcolofthatrange,0),
match(someothercell,firstrowofthatrange))

that will result in something that looks more like:
=index(somerange,row#,column#)
THX Aruds for your reply.
I'm not quite understand.
Can you please explain your formula.
Index D2 to F3 is the table, and match A1 with the D1 to F1 then A2
with c2to C3. but what if I want to want to excel to find both and
diplay it in the same cell?
Will the following formula work ?
=INDEX(A1:J10,MATCH(A1,A2,A1:J10,0))

Ardus said:
Say your lookup table is in C1:F3
Enter in A4:
=INDEX(D2:F3,MATCH(A1,D1:F1,0),MATCH(A2,C2:C3))

HTH
--
AP

"augustus" <[email protected]> a
écrit
dans le message de (e-mail address removed)...
The formula worked very well.
As you can see I am totally a noob to excel. And I have another noob
problem needed to be solve. can someone please help me once more.
Quetion #2: per say, I have a table of data, and I would like to
excel
to return the matching values from the table to a2.

for example:
[ ]|Y1 |Y2| Y3|
X1|.54|.65|.67|
X2|.56|.67|.89|

cell A1=Y1
cell A2=X1
A4 will return the matching values from y1,x1=.54

Thx for the help everyone.
Highly Appreciated.
Click to expand...
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dave!!!

I got another question that similar to the matching function(I'd assum
it is).

I would like excel to lookup and match the approximate value if ther
aren't any true values from another sheet.

Eg:
Sheet 1 =table of data
[] A|B|C
1|4|
2|5|
3|6|
=================
Sheet 2=directory
A1=3.5

A2=the approximate value from sheet1 is 4
Therefore, in cell A2 will = to 4

I've tried the vlookup function but it returned N/
 
You can specify different options in =vlookup()--check the 4th parm (false or
true) in excel's help.

You can do the same thing with the third parm in =match(), too--look at excel's
help, too.

But maybe you want to round the lookup value???

=index(a1:a99,match(round(a1,0),b1:b99,0))


Thanks Dave!!!

I got another question that similar to the matching function(I'd assume
it is).

I would like excel to lookup and match the approximate value if there
aren't any true values from another sheet.

Eg:
Sheet 1 =table of data
[] A|B|C
1|4|
2|5|
3|6|
=================
Sheet 2=directory
A1=3.5

A2=the approximate value from sheet1 is 4
Therefore, in cell A2 will = to 4

I've tried the vlookup function but it returned N/A
Click to expand...
 
Back
Top