Lookup (2000)

Topic

New Reply

Edited by HansV to present data in table format

Hello I have a table on one sheet, I would like to return on a row in another a few cell values from a row in the table. The ‘chosen’ row will be driven by two cell values in that row…in context:

	A	B	C	D
1	XX	YY	30	40
2	AA	BB	30	10
3	EE	FF	40	10
4	ZZ	LL	40	9

I need to return a row for values in columns A and B to my other sheet for the lowest cell value in D provided the value in C for that row is 30. I.e from this table I need AA in A2 and BB B2 to be displayed on my other sheet since 10 is the lowest from D where the C value is 30.
Hope this makes sense, many thanks Darren.

Reply | Quote

Replies

I’d use some intermediate formulas. See attached demo.

Reply | Quote

(No Text)

Reply | Quote

1) The intermediate formulas are array formulas, i.e. you have to confirm them with Ctrl+Shift+Enter. You can see that they are array formulas in my sample workbook because there are braces { } around the formula. (Note: you shouldn’t type those braces yourself, you must confirm the formula with Ctrl+Shift+Enter to make it into an array formula)

2) Your formulas are inconsistent.
In cell E31 on Sheet1, you refer to L10:L1010 and M10:M1010, but then in E32 you refer to H31:H354 and I31:I35
Also, you use E30&”|”&31 instead of E30&”|”&E31

Reply | Quote

thanks Hans, now resolved. First time I have used arrays so a valuable lesson has been had.

Kind regards Darren.

Reply | Quote

Observe that I added another record to the sample you provided.

Sheet2…

B1: 30

B2: 1

B3:

Control+shift+enter…

=SUM(IF(Sheet1!C2:C6=B1,IF(Sheet1!D2:D6=MIN(IF(Sheet1!C2:C6=B1,Sheet1!D2:D6)),1)))

A5:

Control+shift+enter…

=IF(ROWS($A$5:A5)<=$B$3,SMALL(IF(Sheet1!$C$2:$C$6=$B$1,IF(Sheet1!$D$2:$D$6=MIN(IF(Sheet1!$C$2:$C$6=$B$1,Sheet1!$D$2:$D$6)),ROW(Sheet1!$D$2:$D$6)-ROW(Sheet1!$D$2)+1)),ROWS($A$5:A5)),"")

and copy down.

B5, copy across then down:

=IF(N($A5),INDEX(Sheet1!A$2:A$6,$A5),"")

Reply | Quote