I could just behaving a very thick day but help would be very much
appreciated.

I have the following:

Sheet: PHSTB
Column A Column B Column C
1 J100 20 500
2 J100 30 100
3 J100 50 600
4 K200 20 700
5 K200 50 50

Sheet:YTD

Column A Column B Column C
1 J100 K100 K200

2 20
3 30
4 50

So I need the approiate data in the right cells on YTD and blanks where there
are no values or does not exsist. I have the following so far in cell A2:

=IF (VLOOKUP(A1,PHSTB!A1:D72,2,FALSE)="20",VLOOKUP(A2, PHSTB!A1:D72,3,FLASE),
"") this works nicely in A2 but when I drop it down to A3 and change it to
"30" of course it does not work as it only see the first line and gives a
result of "", how can I get this to read all the lines until it match's.

I hope this is clear.

Please let me know if you need any further info, and sorry if this has been
answered before but I couldn't track it down.

Thanks

Jane

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

use absolute reference isntead, change A1:D72 to $A$1:$D$72


"petes_girl via OfficeKB.com" wrote:

I could just behaving a very thick day but help would be very much
appreciated.

I have the following:

Sheet: PHSTB
Column A Column B Column C
1 J100 20 500
2 J100 30 100
3 J100 50 600
4 K200 20 700
5 K200 50 50

Sheet:YTD

Column A Column B Column C
1 J100 K100 K200

2 20
3 30
4 50

So I need the approiate data in the right cells on YTD and blanks where there
are no values or does not exsist. I have the following so far in cell A2:

=IF (VLOOKUP(A1,PHSTB!A1:D72,2,FALSE)="20",VLOOKUP(A2, PHSTB!A1:D72,3,FLASE),
"") this works nicely in A2 but when I drop it down to A3 and change it to
"30" of course it does not work as it only see the first line and gives a
result of "", how can I get this to read all the lines until it match's.

I hope this is clear.

Please let me know if you need any further info, and sorry if this has been
answered before but I couldn't track it down.

Thanks

Jane

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

Thanks for that but it still only reads the first line of each ie J100 20

Teethless mama wrote:
use absolute reference isntead, change A1:D72 to $A$1:$D$72

I could just behaving a very thick day but help would be very much
appreciated.
[quoted text clipped - 34 lines]

Jane

--
Message posted via http://www.officekb.com

Debra Dalgleish shows how to use =index(match()) for this:
http://www.contextures.com/xlFunctions03.html

I think that's what you need/want.

"petes_girl via OfficeKB.com" wrote:

I could just behaving a very thick day but help would be very much
appreciated.

I have the following:

Sheet: PHSTB
Column A Column B Column C
1 J100 20 500
2 J100 30 100
3 J100 50 600
4 K200 20 700
5 K200 50 50

Sheet:YTD

Column A Column B Column C
1 J100 K100 K200

2 20
3 30
4 50

So I need the approiate data in the right cells on YTD and blanks where there
are no values or does not exsist. I have the following so far in cell A2:

=IF (VLOOKUP(A1,PHSTB!A1:D72,2,FALSE)="20",VLOOKUP(A2, PHSTB!A1:D72,3,FLASE),
"") this works nicely in A2 but when I drop it down to A3 and change it to
"30" of course it does not work as it only see the first line and gives a
result of "", how can I get this to read all the lines until it match's.

I hope this is clear.

Please let me know if you need any further info, and sorry if this has been
answered before but I couldn't track it down.

Thanks

Jane

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1

--

Dave Peterson

Dave, thanks for that I'll let you know. I have taken a quick look and it
seems to be what I'm after but I'll let you know.

I'm off out now

Dave Peterson wrote:
Debra Dalgleish shows how to use =index(match()) for this:
http://www.contextures.com/xlFunctions03.html

I think that's what you need/want.

I could just behaving a very thick day but help would be very much
appreciated.
[quoted text clipped - 38 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1


--
Message posted via http://www.officekb.com

Dave

Thanks very much it was just what I needed

petes_girl

Dave Peterson wrote:
Debra Dalgleish shows how to use =index(match()) for this:
http://www.contextures.com/xlFunctions03.html

I think that's what you need/want.

I could just behaving a very thick day but help would be very much
appreciated.
[quoted text clipped - 38 lines]
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200701/1


--
Message posted via http://www.officekb.com