Lookup Issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jayz - 08 May 2008 01:16 GMT

I have a problem that I require help on.

I am working on a big spreadsheet. I have to pull information from one sheet
into another. However I have 2 lookups to do. Is this possible.

example - Info Sheet
A B C
1 Manchester ABC Total -230.30
2 Manchester DEF Total +123.34

Example - Working Sheet

A B C
1 Store Name ABC Total DEF Total
2 Manchester

I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell.

Any Ideas if this is possible, or another solution!!

Thanks in advance

Jayz

Reply to this Message

Pete_UK - 08 May 2008 01:40 GMT

I find it easier with something like this to insert a new column C in
the Info sheet and to concatenate column A and B together to give a
unique reference, i.e.:

=A1&B1

copied down.

Now you can use columns C and D as your lookup table. A typical
formula for the layout you show would be something like this in B2 of
the Working sheet:

=VLOOKUP($A2&B$1,Info!$C:$D,2,0)

Copy into C2, then down if required.

Hope this helps.

Pete

> I have a problem that I require help on.
>
[quoted text clipped - 19 lines]
>
> Jayz

Reply to this Message

Jayz - 08 May 2008 01:52 GMT

Thanks for your prompt responce.

Unfortunately, the suggestion doesn't help me as the spread sheet is also
feeding other information utilitising the "Branch" names.

I wish I'd never started this project!! Ha Ha

Regards

Jayz

> I find it easier with something like this to insert a new column C in
> the Info sheet and to concatenate column A and B together to give a
[quoted text clipped - 39 lines]
> >
> > Jayz

Reply to this Message

Pete_UK - 08 May 2008 09:22 GMT

You will still have the branch information in columns A and B, so you
can still do that.

Pete

> Thanks for your prompt responce.
>
[quoted text clipped - 52 lines]
>
> - Show quoted text -

Reply to this Message

Jim Cone - 08 May 2008 01:46 GMT

You could join the two columns together in a third column and do the look up in that column...
=B1 & C1 would give you "ManchesterABC Total" to lookup.

Signature

Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

"Jayz" <Jayz@discussions.microsoft.com>
wrote in message
I have a problem that I require help on.

I am working on a big spreadsheet. I have to pull information from one sheet
into another. However I have 2 lookups to do. Is this possible.

example - Info Sheet
A B C
1 Manchester ABC Total -230.30
2 Manchester DEF Total +123.34

Example - Working Sheet

A B C
1 Store Name ABC Total DEF Total
2 Manchester

I need to lookup "Manchester" and "ABC Total" to give me "-230.30" in a cell.

Any Ideas if this is possible, or another solution!!

Thanks in advance

Jayz

Reply to this Message

T. Valko - 08 May 2008 02:46 GMT

*Maybe* this...

Entered on the Working sheet in cell B2:

=SUMPRODUCT(--(Info!$A$1:$A$5=$A2),--(Info!$B$1:$B$5=B$1),Info!$C$1:$C$5)

Copy across then down as needed.

Signature

Biff
Microsoft Excel MVP

>I have a problem that I require help on.
>
[quoted text clipped - 21 lines]
>
> Jayz

Reply to this Message