Number and text in same cell

[JBA512]

Hi

I need a formula to do the following example


A2.....6 Steven..........B2.....1..........C2....."blank"
A3.....2 Gary.............B3.....2..........C3.....2
A4.....4 Luke.............B4.....3..........C4....."blank"
A5.....5 Paul..............B5.....4..........C5.....3
A6.....1 George..........B6.....5..........C6.....4
A7.....3 Adam............B7.....6..........C7.....1

so i need C cells to look if the A cells have a certain number in B cells then give it a number in C cells according to the order

also if possible i'd like the data above in "A" to be on another sheet eg. "A" on sheet1 and "B & C" on sheet2

hope this made sense

thanks in advance

jamie

[dilipandey]

Hi Jamie,

Try using below formula:-

Please Login or Register  to view this content.

See attached vlookup with increasing range.xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[Marcol]

If Column A is always "number" space "some text"

Please Login or Register  to view this content.

To handle errors. In C2, Drag/Fill Down

2003
=IF(ISERROR(formula),"",formula)

2007
=IFERROR(formula,"")

[JBA512]

those didn't seem to work for me

i have decided to extract the numbers from the data now into other cells

having trouble with this formula displaying a false

Please Login or Register  to view this content.

i have the numbers 1,2,3,4,5,6,7,8,9,10 in cells a3 down to a12

i have numbers between 1 and 10 in cells a21 to a24

[Marcol]

Why not post a sample workbook, showing your layout, and expected results?

When you are in Advanced reply, click on the paperclip to open the upload window.

View Pic

[JBA512]

here is the file i'm working on

i plan to add more later on but wanting to get the basic data working right first

[dredwolf]

Try this in A3, drag down :

Please Login or Register  to view this content.

Hope this helps

-Edit-
this in c3 will work if you drag down and across

Please Login or Register  to view this content.

[JBA512]

thanks that works

i just tweaked it a little bit to drag across too

Please Login or Register  to view this content.

[dredwolf]

yes, I did same in my edit

Glad it worked for you