Prev: slope?
Next: Consolidating Data Between Worksheets with Duplicates Removed
From: PaulQ on 12 Mar 2010 00:06
Can someone please help me find a formula (or two) for this example. If you
can show me a couple of ways to do this (so I can learn), I'd greatly
appreciate it! Thanks!

I'm trying to figure out a formula (or two) that will help me auto-populate
the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
"8888" row to return blank, since it does not exist in Sheet 1.

Sheet 1:

ColumnA ColumnB

1234 20
4321 10
5678 11
8765 -
9999 12
7777 13

Sheet 2:

ColumnA ColumnB

5678 ?
8765 ?
1234 ?
4321 ?
8888 ?
From: T. Valko on 12 Mar 2010 00:23
>Sheet 1:
>ColumnA ColumnB
>8765 -

Is that "dash" entered in the cell or does it represent an empty cell?

>Sheet 2:
>ColumnA ColumnB
>8765 ?

So, what result should appear on Sheet2 for 8765?

--
Biff
Microsoft Excel MVP


"PaulQ" <PaulQ(a)discussions.microsoft.com> wrote in message
news:C5F93E5F-6C54-4354-9891-5C4C3E360A58(a)microsoft.com...
> Can someone please help me find a formula (or two) for this example. If
> you
> can show me a couple of ways to do this (so I can learn), I'd greatly
> appreciate it! Thanks!
>
> I'm trying to figure out a formula (or two) that will help me
> auto-populate
> the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> "8888" row to return blank, since it does not exist in Sheet 1.
>
> Sheet 1:
>
> ColumnA ColumnB
>
> 1234 20
> 4321 10
> 5678 11
> 8765 -
> 9999 12
> 7777 13
>
> Sheet 2:
>
> ColumnA ColumnB
>
> 5678 ?
> 8765 ?
> 1234 ?
> 4321 ?
> 8888 ?


From: Fred Smith on 12 Mar 2010 00:36
This should do what you want:
=if(isna(vlookup(a1,sheet1!A:B,2,false)),"",vlookup(a1,sheet1!A:B,2,false))

Regards,
Fred

"PaulQ" <PaulQ(a)discussions.microsoft.com> wrote in message
news:C5F93E5F-6C54-4354-9891-5C4C3E360A58(a)microsoft.com...
> Can someone please help me find a formula (or two) for this example. If
> you
> can show me a couple of ways to do this (so I can learn), I'd greatly
> appreciate it! Thanks!
>
> I'm trying to figure out a formula (or two) that will help me
> auto-populate
> the ? fields within ColumnB of Sheet 2. Also, I want the ? value for the
> "8888" row to return blank, since it does not exist in Sheet 1.
>
> Sheet 1:
>
> ColumnA ColumnB
>
> 1234 20
> 4321 10
> 5678 11
> 8765 -
> 9999 12
> 7777 13
>
> Sheet 2:
>
> ColumnA ColumnB
>
> 5678 ?
> 8765 ?
> 1234 ?
> 4321 ?
> 8888 ?

 | 
Pages: 1
Prev: slope?
Next: Consolidating Data Between Worksheets with Duplicates Removed