If lookup is true, return certain columns from same row?

[tcwfraser]

Hi all,

I apologise if this topic has been covered previously. I have looked and not found anything, and I am nearly at my wit's end.

What I am attempting to do is perform a lookup based on an input value, and then return certain columns if the lookup is true.

For example, in my data source let's say I have columns A through Z, all populated. I would like to have a sheet where I can say if A1 matches "textstring", give me C1, H1, etc. There are in excess of 9000 rows that this has to be performed on.

I know enough that this is something to do with array / CSE formulae / vlookup, but I don't know how to fix it.

Any suggestions would be greatly appreciated . . . by myself and my boss.

Thanks,
Trevor

 

[Dave Patton]

On 2002-08-26 12:30, tcwfraser wrote:
Hi all,

I apologise if this topic has been covered previously. I have looked and not found anything, and I am nearly at my wit's end.

What I am attempting to do is perform a lookup based on an input value, and then return certain columns if the lookup is true.

For example, in my data source let's say I have columns A through Z, all populated. I would like to have a sheet where I can say if A1 matches "textstring", give me C1, H1, etc. There are in excess of 9000 rows that this has to be performed on.

I know enough that this is something to do with array / CSE formulae / vlookup, but I don't know how to fix it.

Any suggestions would be greatly appreciated . . . by myself and my boss.

Thanks,
Trevor

=VLOOKUP(A9,A2:D6,{2,3,4},0)

This formula Array entered in three cells
returns the values in the cited columns.

Array enter with Ctrl-Shift-Enter.

Adjust the ranges as necessary and try the above. Then expand/refine as necessary.

 

[tcwfraser]

Sigh.

It's quite humbling to find out the answer was so simple. Nonetheless, I extend my thanks for the speedy resolution.

Regards,
Trevor

 

[Dave Patton]

On 2002-08-26 13:08, tcwfraser wrote:
Sigh.

It's quite humbling to find out the answer was so simple. Nonetheless, I extend my thanks for the speedy resolution.

Regards,
Trevor

additional points

a)I just read a message that you might find useful.

following reference is just one line

http://groups.google.com/groups?hl=en&lr=&ie=UTF8&selm=#HXmDRqcBHA.1960@tkmsftngp03

b) If you are using a test to see if criteria exists such is =IF(ISNA(VLOOKUP(A11,LTable,{2,3},0))),"Not Found", ....
The computer is doing the calculation twice
consider

=IF(ISNA(SETV(VLOOKUP(A11,LTable,{2,3},0))),"Not Found",GETV())

The Setv stores the information.

Search for Mr. Longre's Morefunc addin.

Download the morefunc add-in from

http://longre.free.fr/english/index.html

and install/activate the add-in via Tools|Add-Ins.



c) If your information is sorted, you may be able to use the True parameter.

Please advise which alternative gives you the best results.
This message was edited by Dave Patton on 2002-08-26 14:55