Help with Vlookup and if

[petes_girl via OfficeKB.com]

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!A172,2,FALSE)="20",VLOOKUP(A2,PHSTB!A172,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

 

[Teethless mama]

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

 

[petes_girl via OfficeKB.com]

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

use absolute reference isntead, change A172 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

 

[Dave Peterson]

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]

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

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.aspx/excel-functions/200701/1

 

[petes_girl via OfficeKB.com]

Dave

Thanks very much it was just what I needed

petes_girl

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.aspx/excel-functions/200701/1