Help - Easy Q not sure if need IF or LOOKUP

[Bran987]

Hi guys!

I need a formula that does this:

OK I'm going to draw a spreadsheet
......A..........B......
1..100.....dog....
2..150...<blank>
3..200......cat.....

so all I want is a formula that looks in Column B and if there is ANY text at all, returns the value in Column A

So I want it to look into B1 and it will see text there and return "100", but if it looks in B2 and sees that it is blank, it just won't return anything.

Can I do this? Seems like it would be a combination of IF and LOOKUP or something.

Thank you.

[wa284sj]

Hi Bran,

put the data you have in column A into column C and enter the following formula in A1

=IF(B1="","",C1)

copy it down to A2 & A3

Stuart

[CLR]

In C1 put this formula and copy down........

=IF(B1="","",A1)

or

=IF(ISTEXT(B1),A1,"")

Vaya con Dios,
Chuck, CABGx3




"Bran987" <[email protected]> wrote in
message news:[email protected]...
>
> Hi guys!
>
> I need a formula that does this:
>
> OK I'm going to draw a spreadsheet
> .....A..........B......
> 1..100.....dog....
> 2..150...<blank>
> 3..200......cat.....
>
> so all I want is a formula that looks in Column B and if there is ANY
> text at all, returns the value in Column A
>
> So I want it to look into B1 and it will see text there and return
> "100", but if it looks in B2 and sees that it is blank, it just won't
> return anything.
>
> Can I do this? Seems like it would be a combination of IF and LOOKUP
> or something.
>
> Thank you.
>
>
> --
> Bran987
> ------------------------------------------------------------------------
> Bran987's Profile:
http://www.excelforum.com/member.php...fo&userid=7503
> View this thread: http://www.excelforum.com/showthread...hreadid=375853
>

[Bryan Hessey]

I am confused as to why you wish to do this.

If you wish a list of numbers which have entries in column B would you not sort over column B and block-delete all rows with a blank column B, then delete column B, and re-sort (if required) over column A.

If, in fact, you wish to use this data as a lookup-range to update data on a different sheet, then:
assuming you are using the numbers in column A as the lookup value, the lookup would read something like
=IF(ISNA(VLOOKUP(a1,looktable,1,FALSE)),"Missing "&a1,IF(VLOOKUP(B5,looktable,2)="","",a1))