help with matching if

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

augustus - 14 Jul 2006 10:06 GMT

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

Reply to this Message

Ardus Petus - 14 Jul 2006 10:33 GMT

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" <augustus.2axh6o_1152868203.1605@excelforum-nospam.com> a écrit
dans le message de news:
augustus.2axh6o_1152868203.1605@excelforum-nospam.com...

> The formula worked very well.
> As you can see I am totally a noob to excel. And I have another noob
[quoted text clipped - 13 lines]
> Thx for the help everyone.
> Highly Appreciated.

Reply to this Message

augustus - 15 Jul 2006 20:44 GMT

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 Petus Wrote:

> Say your lookup table is in C1:F3
> Enter in A4:
[quoted text clipped - 32 lines]
> >
> ------------------------------------------------------------------------

Signature

augustus

Reply to this Message

augustus - 15 Jul 2006 20:59 GMT

> Say your lookup table is in C1:F3
> Enter in A4:
[quoted text clipped - 32 lines]
> >
> ------------------------------------------------------------------------

Signature

augustus

Reply to this Message

Dave Peterson - 15 Jul 2006 21:08 GMT

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.
[quoted text clipped - 48 lines]
> augustus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36352
> View this thread: http://www.excelforum.com/showthread.php?threadid=561396

Signature

Dave Peterson

Reply to this Message

Dave Peterson - 15 Jul 2006 21:18 GMT

I dropped a 0:

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

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

> Try...
>
[quoted text clipped - 62 lines]
>
> Dave Peterson

Signature

Dave Peterson

Reply to this Message

augustus - 15 Jul 2006 23:13 GMT

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/

Reply to this Message

Dave Peterson - 15 Jul 2006 23:27 GMT

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!!!
>
[quoted text clipped - 24 lines]
> augustus's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=36352
> View this thread: http://www.excelforum.com/showthread.php?threadid=561396

Signature

Dave Peterson

Reply to this Message

Rate this thread: