MATCH in Excel 1

[Wray69]

ok, say I had data similar to the following;

Num1 Ret1 Ret2 Num2 Ret3 Ret4 Match
1 21 31 5 41 51 4
2 22 32 6 42 52 1
3 23 33 7 43 53 5
4 24 34 8 44 54 5


If the # in the Match column matchs a # in Num1 Column in need to return the coresponding #'s in ret1 and ret2, but if it matchs the a # in the Num2 Column I need to return the coresponding in ret3 and ret4.

I have been trying something similar to this =INDEX($L$15:$Q$18,MATCH(R15,$L$15:$L$18,0),2)which works if it matchs the Num1 column but if the # is in the Num2 column I get a #/NA.

Any help would be greatly appreciated.

Regards,

Wray

 

[DaleWatson123321]

Hi Wray,

I placed your data in A1:G5, and I added two additional fields, named Ans1 and Ans2 (in H:I)

Enter the following formula in H2:
=IF($G2=$A2,B2,IF($G2=$E2,E2,"&quot)

Then copy the formula to I2, and then down for the rows required.

I hope this is what you required. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Wray69]

Thanks Dale,

I should of been more specific though, I need this to be and array. the match could be anywhere in the column.

Regards,
Wray

 

[Zathras]

Try this...

S14: 'Result1

T14: 'Result2

S15: =IF(ISNA(VLOOKUP($R15,$L$15:$N$18,2,0)),VLOOKUP($R15,$O$15:$Q$18,2,0),VLOOKUP($R15,$L$15:$N$18,2,0))

T15: =IF(ISNA(VLOOKUP($R15,$L$15:$N$18,2,0)),VLOOKUP($R15,$O$15:$Q$18,3,0),VLOOKUP($R15,$L$15:$N$18,3,0))

Copy both formulas from row 15 down as far as you need.

MATCH returns the row number, VLOOKUP returns the value. (But be careful with VLOOKUP: If the first column of the lookup table is not in sequence be sure to specify 0 or False for the fourth parameter - don't leave it blank.)

 

[Wray69]

Thanks Zathras,

That worked great... Star for you....

 

[xlhelp]

Realize you already have a solution from Zathras that works great.

Here is another one
Data is in A2:G5 with row having the column headers
Cell H2
=IF(ISERROR(MATCH($G2,$A$2:$A$5,0)),INDEX($E$2:$F$5,MATCH($G2,$D$2:$D$5,0),1),INDEX($B$2:$C$5,MATCH($G2,$A$2:$A$5,0),1))
Cell I2
=IF(ISERROR(MATCH($G2,$A$2:$A$5,0)),INDEX($E$2:$F$5,MATCH($G2,$D$2:$D$5,0),2),INDEX($B$2:$C$5,MATCH($G2,$A$2:$A$5,0),2))

 

[SkipVought]

Hi,

Here's amoher take similar to xlhelps. But I like to use and encocourage the use of Range Names.

1. I used Insert/Name/Create to create names consistant with your headings

2. Then the corresponding formulae to copy down are...

=IF(ISNA(MATCH(Match,Num1,0)),IF(ISNA(MATCH(Match,Num2,0)),"",INDEX(Ret3,MATCH(Match,Num2,0),1)),INDEX(Ret1,MATCH(Match,Num1,0),1))

and 

=IF(ISNA(MATCH(Match,Num1,0)),IF(ISNA(MATCH(Match,Num2,0)),"",INDEX(Ret4,MATCH(Match,Num2,0),1)),INDEX(Ret2,MATCH(Match,Num1,0),1))

Hope this adds to the information you need Skip,
SkipAndMary1017@mindspring.com