Index/match

[EVR_99]

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 384pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=512 colSpan=8 height=20>INDEX('Lap 1-20'!$A$4:$C$46,MATCH('Fast Laps'!E4,'Lap 1-20'!$E$4:$E$46,0),3)</TD></TR></TBODY></TABLE>

The formula above found what I was looking for, only because 'fast laps E4' matched a number within E4:E46, however what I'm really trying to do is find E4 which could be found anywhere within E4:N46

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=512 border=0><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 384pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=512 colSpan=8 height=20>INDEX('Lap 1-20'!$A$4:$C$46,MATCH('Fast Laps'!E4,'Lap 1-20'!$E$4:$N$46,0),3)

Can match use a multitude of rows or columns?

Thanks,
David Lanne
</TD></TR></TBODY></TABLE>

 

[Aladin Akyurek]

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 384pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 width=512 colSpan=8>INDEX('Lap 1-20'!$A$4:$C$46,MATCH('Fast Laps'!E4,'Lap 1-20'!$E$4:$E$46,0),3)</TD></TR></TBODY></TABLE>

The formula above found what I was looking for, only because 'fast laps E4' matched a number within E4:E46, however what I'm really trying to do is find E4 which could be found anywhere within E4:N46

<TABLE style="WIDTH: 384pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512><COLGROUP><COL style="WIDTH: 48pt" span=8 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 384pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; mso-ignore: colspan" height=20 width=512 colSpan=8>INDEX('Lap 1-20'!$A$4:$C$46,MATCH('Fast Laps'!E4,'Lap 1-20'!$E$4:$N$46,0),3)

Can match use a multitude of rows or columns?

Thanks,
David Lanne

</TD></TR></TBODY></TABLE>

Control+shift+enter, not just enter:

=INDEX('Lap 1-20'!$C$4:$C$46,MIN(IF('Lap 1-20'!$E$4:$N$46=E4,ROW('Lap 1-20'!$C$4:$C$46)-ROW('Lap 1-20'!$C$4)+1)))

This formula returns the value associated with the first match it encounters.