LOOKUP with multiple matches and blank cells

[pheonixshef04]

Hello, I am working on a spreadsheet in where there are multiple matches and values. I am having problems with creating a formula to lookup the last non-blank cell



Below is an example of the raw data

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Doe, Jane
[/TD]
[TD]1/06/2015
[/TD]
[TD]7.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]113.9
[/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]1/13/2015
[/TD]
[TD]8.9
[/TD]
[TD]61
[/TD]
[TD]1.14
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]1/20/2015
[/TD]
[TD]9.4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]106.6
[/TD]
[/TR]
[TR]
[TD]Doe, Jane
[/TD]
[TD]1/27/2015
[/TD]
[TD]9.5
[/TD]
[TD]64
[/TD]
[TD]1.63
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank, Mark
[/TD]
[TD]1/06/2015
[/TD]
[TD]8.4
[/TD]
[TD]63
[/TD]
[TD]1.17
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank, Mark
[/TD]
[TD]1/13/2015
[/TD]
[TD]8.6
[/TD]
[TD][/TD]
[TD][/TD]
[TD]129.5
[/TD]
[/TR]
[TR]
[TD]Frank, Mark
[/TD]
[TD]1/20/2015
[/TD]
[TD]8.3
[/TD]
[TD]65
[/TD]
[TD]1.35
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Frank, Mark
[/TD]
[TD]1/27/2015
[/TD]
[TD]9.9
[/TD]
[TD][/TD]
[TD][/TD]
[TD]129.2
[/TD]
[/TR]
</tbody>[/TABLE]














So I use LOOKUP(2,1/(Raw1!$A$1:$H$2000=Roster!A6),Raw1!$E:$E) and it works great in getting the values in where there is data in all columns (2 and 3). However if I need to grab the last non-blank value in the fourth column for a particular person, I get a value of 0.




Also, I need it to grab the date. For an example: For Frank, Mark I need the date (1/20/15) in which the value in the fourth column was given (65).

I am using Excel 2010 with Windows 7

Thank you very much in advance.

 

[venkat1926]

not fully understood your requirement

see below and column G. formula in G2 is copied down.

Sheet1

*	A	B	C	D	E	F	G
hdng1	hdng2	hdng3	hdng4	hdng5	hdng6	reqd
Doe, Jane	*	*	*
Doe, Jane	*
Doe, Jane	*	*	*
Doe, Jane	*
Frank, Mark	*
Frank, Mark	*	*	*
Frank, Mark	*
Frank, Mark	*	*	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:64px;"><col style="width:80px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]1/6/2015[/TD]
[TD="align: right"]7.6[/TD]

[TD="align: right"]113.9[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]1/13/2015[/TD]
[TD="align: right"]8.9[/TD]
[TD="align: right"]61[/TD]
[TD="align: right"]1.14[/TD]

[TD="align: right"]01/13/15[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]1/20/2015[/TD]
[TD="align: right"]9.4[/TD]

[TD="align: right"]106.6[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]1/27/2015[/TD]
[TD="align: right"]9.5[/TD]
[TD="align: right"]64[/TD]
[TD="align: right"]1.63[/TD]

[TD="align: right"]01/27/15[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]1/6/2015[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]1.17[/TD]

[TD="align: right"]01/06/15[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]1/13/2015[/TD]
[TD="align: right"]8.6[/TD]

[TD="align: right"]129.5[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]1/20/2015[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]65[/TD]
[TD="align: right"]1.35[/TD]

[TD="align: right"]01/20/15[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]1/27/2015[/TD]
[TD="align: right"]9.9[/TD]

[TD="align: right"]129.2[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula G2 =IF(D2<>"",B2,"") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[bosco_yip]

Try :

=LOOKUP(9.9E+307,OFFSET(C1,MATCH(1,INDEX((A1:A9="Frank, Mark")/(B1:B9=--"1/20/15"),),)-1,,,4))

 

[Aladin Akyurek]

Also, without volatile OFFSET...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td]Doe, Jane[/td][td]

1/6/2015​

[/td][td]

7.6​

[/td][td][/td][td][/td][td]

113.9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Doe, Jane[/td][td]

1/13/2015​

[/td][td]

8.9​

[/td][td]

61​

[/td][td]

1.14​

[/td][td][/td][td][/td][td]Frank, Mark[/td][td]

1/20/2015​

[/td][td]

1.35​

[/td][/tr]

[tr][td]

3​

[/td][td]Doe, Jane[/td][td]

1/20/2015​

[/td][td]

9.4​

[/td][td][/td][td][/td][td]

106.6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

4​

[/td][td]Doe, Jane[/td][td]

1/27/2015​

[/td][td]

9.5​

[/td][td]

64​

[/td][td]

1.63​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

5​

[/td][td]Frank, Mark[/td][td]

1/6/2015​

[/td][td]

8.4​

[/td][td]

63​

[/td][td]

1.17​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td]Frank, Mark[/td][td]

1/13/2015​

[/td][td]

8.6​

[/td][td][/td][td][/td][td]

129.5​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Frank, Mark[/td][td]

1/20/2015​

[/td][td]

8.3​

[/td][td]

65​

[/td][td]

1.35​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]Frank, Mark[/td][td]

1/27/2015​

[/td][td]

9.9​

[/td][td][/td][td][/td][td]

129.2​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

G2, control+shift+enter, not just enter:

=LOOKUP(9.99999999999999E+307,INDEX($C$1:$F$8,MATCH($I2,IF($A$1:$A$8=$H2,$B$1:$B$8),0),0))

where I2 = Frank, Mark and H2 = 1/20/2015.