Software

General discussion

Locked

Join the Microsoft Office Suite discussion

By ·
This is the fifth discussion thread of our ongoing Microsoft Office Suite discussion, where you can post your Word, Excel, or Access questions, enter comments about our weekly Microsoft Office Suite e-newsletter, and offer suggestions for future tips.

Paste the following URLs into a browser to check out our previous discussions. (Please remove any superfluous spaces when you copy and paste these links.)

http://nl.com.com/acct_mgmt.jsp?brand=techrepublic

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

Collapse -

excel: vlookup

by In reply to Join the Microsoft Office ...

VLOOKUP defalults to the next lowest vlookup_value if the vlookup_value looked for is not found. Is there a way to cause it to go to the next highest number instead?

Collapse -

Vlookup with IF()

by In reply to excel: vlookup

=if(vlookup(A1,\$H1:\$J10,1)<A1,
vlookup(A1+X,\$H1:\$J10,2), vlookup(A1,\$H1:\$J10,2)
Compare the value in A1 against values in the table H1:J10;
if the value in H1:H10 is less than A1, add some value to
A1, do the lookup again, and use the corresponding value
in J1:J10; else, do the lookup and use the correspoding
value in J1:J10.

Collapse -

Nice try!

by In reply to Vlookup with IF()

The problem is what value of X to use. If it's too low then the second lookup will get the same value, too high and it could "overshoot". There is also the difficulty of handling the case where the value in A1 is greater than the highest value in H1:H10. In my opinion, this probelm can only be solved with Visual Basic but I would be very interested in being proved wrong.

Collapse -

this worked for the original poster

by In reply to Nice try!

dblaney, you are right - this is a quick shot at solving the
problem. I tried it out with several scenarios, and the
results were good if the table was 'regular' - the values
stepped up in fixed increments. This solution didn't work
for irregular expressions n a consistent manner, as you
pointed out.

However, I did get feedback from the original poster, and it
worked for him. C'est la vie!

Sometimes, a cigar is only a cigar.

Collapse -

Slight modification to Rick from BC's solution

by In reply to Nice try!

To make VLOOKUP pick the next higher value instead of the next lower value, insert a new column 2 into table h1:j10, so table is now h1:k10. Copy h2:h10 into i1:i9 and h10 into i10. This gives a second col whose values are offset by one row from the first col. Now, nest VLOOKUP into VLOOKUP into IF as follows:
=IF(VLOOKUP(A1,\$H\$1:\$H\$10,1)<A1,VLOOKUP(VLOOKUP(A1,\$H\$1:\$I\$10,2),\$H\$1:\$K\$10,colnum),VLOOKUP(A1,\$H\$1:\$K\$10,colnum))
This will handle uneven increments in table, and there shouldn't be any issue if A1 > largest value in table.

Collapse -

Tabs. AArggghhh

by In reply to excel: vlookup

This is nicely aligned

If I click OK to the update box "Word" inserts an additional tab into the table of contents space which messes with the format ~ pushes each line over the edge

If I manually delete the additional tab the alignment of the ToC reverts to the original layout.

The tab is NOT in the style {Heading 2]
I have scrolled through the document. I do not have a tab in that position within the document!

How do I tell Word not to add this tab when I update the ToC?

Regards Paul

Collapse -

Tab issues

by In reply to Tabs. AArggghhh

I have encountered the same problem as you have described above. Check to make sure that Tools -Autocorrect Options - AutoFormat As You Type - the bottom 3rd of the tab, Automatically As You Type -make sure that all 3 boxes are checked. This resolved my issue and I hope that it does yours.

Collapse -

General

by In reply to Join the Microsoft Office ...

While I appreciate the MS Office Suite newsletter, I was wondering if anyone besides me is interested in PowerPoint tips. I certainly have a lot to learn about this application. Anyone from Tech Republic listening?

TIA

Collapse -

PowerPoint tips?

I too would be interested in a PowerPoint tips discussion. I've been wondering if it could be used to email family photos for genealogy purposes and could narrative be added? Would this be simpler than using it to create a CD? I've not attempted it yet.

Collapse -

PowerPoint for family photos

by In reply to PowerPoint tips?

Sure, using PowerPoint to e-mail family photos with or without narrative(s) would be easy. My sister went to Hawaii two years ago and took photos of the schoolhouse where our grandfather was Principal (and where our father was a student), along with many other interesting sights. She e-mailed me a PowerPoint presentation with her photos in it and comments for each shot so I'd know what I was looking at.

If you're going to send the presentation to someone who doesn't have PowerPoint, you can create a presentation file that contains the viewer (a .PPS file). That way folks without PowerPoint can still view your presentation. Instructions on how to do this can be found in the PowerPoint help facility. Be sure to try it out before you send the file to them to be sure it works properly.
Kinda funny... I'm the only one of us "kids" born in Hawaii and I've never been back since we moved here when I was 3.

Related Discussions

• 1

zlzpqx ·

• 1

ITguy1986 ·

• 1

by which software I can make my PD portable?

webtechexprt ·

• 8

what is the best chatbot software for small business?

nathanlohey ·

• 2

Buchiiii ·