lookup formula, 2nd try

[gideon]

Hi,

I'll try and ask a different question....

E.G.
Can I return the value from A1 to cell G4, where C1 = F4...as below
A B C D
Chair Administration 7001 1
Board Administration 7002 7001
Chief Administration 7003 7001
Manager Administration 7004 7003
General Administration 7005 7001

Col G returns......
Chair
Chair
Chief
Chair

Many thanks

[Qroozn]

in cell g4
=IF(C1=f4,a1,"")


[ This Message was edited by: Qroozn on 2002-04-30 20:41 ]

[gideon]

I need to do more than a simple IF()....I need the formula to do this...

=VLOOKUP(F3,C2:E19,-3)

Obviously the -3 offset doesn't work and I ran into circular reference problems whe I tried to use a combination of OFFSET, ADDRESS, ROW and COLUMN....

I'm sure it can be done somehow...

[Yogi Anand]

On 2002-04-30 20:35, gideon wrote:
Hi,

I'll try and ask a different question....

E.G.
Can I return the value from A1 to cell G4, where C1 = F4...as below
A B C D
Chair Administration 7001 1
Board Administration 7002 7001
Chief Administration 7003 7001
Manager Administration 7004 7003
General Administration 7005 7001

Col G returns......
Chair
Chair
Chief
Chair

Many thanks

I don't understand the context in which you are doing it or why you want to do it, however put the following formula in G1 and drag it down ...

=IF($C$1=$F$4,$A$1,"it is not in the cards")

If cell C1 and F4 both house 7001, then the formula written in cells G1:G6 will return

Chair
Chair
Chair
Chair
Chair
Chair

Hope This Helps!

[Qroozn]

The reason you aren't getting responses is because you are confusing the hell out of us.
if you are doing a lookup then the lookupvalue must be in the left most column.

You cannot replace a value in a cell with another value based on a formula(without VBA)

send the spreadsheet page to me with a description of exactly what you want..

[gideon]

Cheers,
I am finding it hard to explain I must admit...maybe I'm asking too much of excel, I'll give it one more go.......

Formula in G4
1) lookup F4 in col E. this could be on any row of col E. e.g. E30
2) return the value in col A on the same row of that found value e.g. A30.

Hope this makes more sense.

[Yogi Anand]

On 2002-04-30 20:55, gideon wrote:
Cheers,
I am finding it hard to explain I must admit...maybe I'm asking too much of excel, I'll give it one more go.......

Formula in G4
1) lookup F4 in col E. this could be on any row of col E. e.g. E30
2) return the value in col A on the same row of that found value e.g. A30.

Hope this makes more sense.

Hi gideon:
You are not asking of Excel too much ... you have to slow down and as our friend Qroozn said look at these things carefully and patiently.
If I use the data that you originally posted with the scenario that you have just described, you don't need a VLOOKUP formula .. what you need is the following formula:

=INDEX(A1:D6,MATCH(F4,C:C),1)

Try to grasp what this formula is doing, play around with it with your own references and post back if it works for you ... otherwise explain a little further and let us takeit from there!

[gideon]

Thanks,

I changed a few references and it works a treat..