Newbie - help needed with lookup, please.

[Dan E]

Hi - help needed please. I need a way to read a text string from one cell
(say Sheet1A1), find it in a list in another sheet (say Sheet2 A17), then
copy a numeric value from a cell alongside the found cell (Sheet2 B17, say)
into another cell on the first sheet (Sheet1B1, say). If the text string
ends in a specific letter combination, the number needs to go into a
different cell (Sheet1C1) - hope that's clearer than mud . The full gory
(to me) problem is posted in "Nursing Schedule workbook" from yesterday - I
guess it was too long winded...

TIA
Dan E
(e-mail address removed)

 

[Frank Kabel]

Hi Dan
enter the following formulas in sheet1:
B1:
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC","",VLOOKUP(A1,
'sheet2'!$A$1:$B$100,2,0))

C1:
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","",VLOOKUP(A1
,'sheet2'!$A$1:$B$100,2,0))

I assumed your letter combination at the end is 'ABC'. Adapt this to
your needs and copy for all rows

 

[Dan E]

Wow - was that quick! Many thanks, Frank - I have to go out, but will be
onto this within a few hours. Again, many thanks. As a final step, if
Sheet1Ax is blank or a hyphen (representing a null entry), is there a way to
have the formulae enter a null value (a hyphen) into the cells that
otherwise would contain a numeric value (Sheet1Bx and Cx)? And place a
hyphen into whichever of Bx or Cx doesn't get the number from the two
formulae you gave?

TIA
Dan E
(e-mail address removed)

 

[Dan E]

Wow - was that quick! Many thanks, Frank - I have to go out, but will be
onto this within a few hours. Again, many thanks. As a final step, if
Sheet1Ax is blank or a hyphen (representing a null entry), is there a way to
have the formulae enter a null value (a hyphen) into the cells that
otherwise would contain a numeric value (Sheet1Bx and Cx)? And place a
hyphen into whichever of Bx or Cx doesn't get the number from the two
formulae you gave?

TIA
Dan E
(e-mail address removed)

 

[Frank Kabel]

Hi
try
B1:
=IF(A1="","",IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC",""
,VLOOKUP(A1,
'sheet2'!$A$1:$B$100,2,0)))

and C1:
=IF(A1="","",IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","
",VLOOKUP(A1
,'sheet2'!$A$1:$B$100,2,0)))

 

[Kevin Stecyk]

Hi Dan,

Just a heads up, your software or something seems to be issuing repeat
identical posts. It seems odd to that these duplicate posts are issued
about a half hour apart. That happened in your "Nursing Schedule Workbook"
in the "public.excel" section too. It's not a big deal, but you might want
to check to see if you can issue just one response instead of several
duplicates.

I am glad to see that you broke up your original message into bite sized
chunks and that Frank is giving you excellent support.

Good luck!

Best regards,
Kevin

 

[Dan E]

Hi Kevin - yes, I noticed that - haven't had that trouble before - Outlook
Express seemed to be having trouble sending messages even after I
refreshed - DSL connection was OK all the time. Maybe I've screwed up some
setting. Anyway - apologies. If it happens again I'll put some work into
troubleshooting the problem.

Regards,

Dan E
(e-mail address removed)

 

[Kevin Stecyk]

Dan,

No big deal. I just thought you might want to keep an eye on it.

Best regards,
Kevin