Multiple reference for lookup in excel
 

I'm looking for an excel command which can lookup data dependent on two
reference columns. eg

A B C D
1 Dave 100
1 A Jim 10
3 C Pete
2 A Alan 30
3 A John Apples
2 B Helen Purple Monkeys

In Cell E1 i would like to enter a value in column A
In Cell F1 i would like to enter a value in column B

So if E1 = 3 and F1 = A then G1 = John and H1= Apples

Any suggestions appreciated


--
Andrew

Andrew,
The easiest way to handle this would be to insert a new column in A so
that now, B has #s, C has letters, D has names and E has amounts/types.
In column a put ...
=B1&C1
to make an index of the 2 search criteria
In F1 (previously E1) put 3
In G1 (previously F1) put A
In H1 put the following formula...
=VLOOKUP($F$1&$G$1,$A:$E,4,FALSE)
In I1 put the following formula...
=VLOOKUP($F$1&$G$1,$A:$E,5,FALSE)

HTH,
Gary Brown


"Andrew" wrote:


I'm looking for an excel command which can lookup data dependent on two
reference columns. eg

A B C D
1 Dave 100
1 A Jim 10
3 C Pete
2 A Alan 30
3 A John Apples
2 B Helen Purple Monkeys

In Cell E1 i would like to enter a value in column A
In Cell F1 i would like to enter a value in column B

So if E1 = 3 and F1 = A then G1 = John and H1= Apples

Any suggestions appreciated


--
Andrew

Copy the new formula in A1 down the range

"Gary Brown" wrote:

Andrew,
The easiest way to handle this would be to insert a new column in A so
that now, B has #s, C has letters, D has names and E has amounts/types.
In column a put ...
=B1&C1
to make an index of the 2 search criteria
In F1 (previously E1) put 3
In G1 (previously F1) put A
In H1 put the following formula...
=VLOOKUP($F$1&$G$1,$A:$E,4,FALSE)
In I1 put the following formula...
=VLOOKUP($F$1&$G$1,$A:$E,5,FALSE)

HTH,
Gary Brown


"Andrew" wrote:


I'm looking for an excel command which can lookup data dependent on two
reference columns. eg

A B C D
1 Dave 100
1 A Jim 10
3 C Pete
2 A Alan 30
3 A John Apples
2 B Helen Purple Monkeys

In Cell E1 i would like to enter a value in column A
In Cell F1 i would like to enter a value in column B

So if E1 = 3 and F1 = A then G1 = John and H1= Apples

Any suggestions appreciated


--
Andrew