Nested Functions-revised question

rsw8679 · Mar 26th, 2002, 10:29 AM

Revised question from yesterday
Work Sheet 1 looks like this:
D3=4", F3=8", A11=8, C11=1977, F11=*, A12=8, C12=1984, D12=*; WHERE *=value to lookup from Work Sheet 2.
Work Sheet 2 looks like this and is sorted in ascending order by columns A & B:
A9=8, B9=1977, C9=8", D9=32
A10=8, B10=1984, C10=4", D10=263
The desire is to populate Work Sheet 1 with F11=32 and D12=263.
I am assuming I will have to use nested IF, LOOKUP functions, but have been unable to get the right combinations, error free.
Any help is greatly appreciated
Randy

[ This Message was edited by: rsw8679 on 2002-03-26 09:32 ]

Tim Francis-Wright · Mar 26th, 2002, 11:47 AM

Here's something ugly but effective:

On Sheet 2, insert a column D between C and old column D and populate it like this:
D8: = A8 & B8 & C8

Now, on Sheet 1, the cells on D and F would be:
F11=VLOOKUP($A11&$C11&F$4,Sheet2!$C$2:$D$999,2,False)
D12=VLOOKUP($A12&$C12&D$4,Sheet2!$C$2:$D$999,2,False)

HTH

_________________
"Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden

[ This Message was edited by: Tim Francis-Wright on 2002-03-26 10:47 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 10:29 AM	#1
rsw8679 New Member Join Date: Mar 2002 Posts: 5	Revised question from yesterday Work Sheet 1 looks like this: D3=4", F3=8", A11=8, C11=1977, F11=, A12=8, C12=1984, D12=; WHERE *=value to lookup from Work Sheet 2. Work Sheet 2 looks like this and is sorted in ascending order by columns A & B: A9=8, B9=1977, C9=8", D9=32 A10=8, B10=1984, C10=4", D10=263 The desire is to populate Work Sheet 1 with F11=32 and D12=263. I am assuming I will have to use nested IF, LOOKUP functions, but have been unable to get the right combinations, error free. Any help is greatly appreciated Randy [ This Message was edited by: rsw8679 on 2002-03-26 09:32 ]

Mar 26th, 2002, 11:47 AM	#2
Tim Francis-Wright Board Regular Join Date: Feb 2002 Posts: 74	Here's something ugly but effective: On Sheet 2, insert a column D between C and old column D and populate it like this: D8: = A8 & B8 & C8 Now, on Sheet 1, the cells on D and F would be: F11=VLOOKUP($A11&$C11&F$4,Sheet2!$C$2:$D$999,2,False) D12=VLOOKUP($A12&$C12&D$4,Sheet2!$C$2:$D$999,2,False) HTH _________________ "Interfere? Of course we should interfere! Always do what you're best at, that's what I say!" -- The Doctor, Nightmare of Eden [ This Message was edited by: Tim Francis-Wright on 2002-03-26 10:47 ]