if/then vlookup with multiple search criteria - new to Excel

#5. I think there is a way, in the "Copy and Paste" sheet to have it so that I start another line following the item when I paste it. Right now, I have a whole bunch of spaces so that it will look like it is starting another line, but that doesn't always work, depending upon the size of the text box into which it's being pasted.
Thanks, so much!!!

Just a quick answer on your #1 question:

If you are having to change the column letter and row number on each cell when you copy the formula, you don't want them to be absolute!

To quickly set a cell reference to be absolute you can just press F4 which puts the dollar signs in for you.

All the dollar signs do is create a "fixed" point for your cell reference, and can be used just on columns, or just on rows or both. An example from your worksheet would be in column E to change your lookup range to absolute values so it would look like:

=VLOOKUP(D2,PURGE!$D$2:$F$382,2,FALSE)

If you do that in D2 then copy it down it "fixes" the range you are looking in.

Does that make sense?

That does!! So using the dollar signs, I will always be able to search the entirety of the Purge worksheet instead of adjusting down 1 row, as it is right now?
Thank you.

Originally Posted by shirleyxls

Just a quick answer on your #1 question:

If you are having to change the column letter and row number on each cell when you copy the formula, you don't want them to be absolute!

To quickly set a cell reference to be absolute you can just press F4 which puts the dollar signs in for you.

All the dollar signs do is create a "fixed" point for your cell reference, and can be used just on columns, or just on rows or both. An example from your worksheet would be in column E to change your lookup range to absolute values so it would look like:

=VLOOKUP(D2,PURGE!$D$2:$F$382,2,FALSE)

If you do that in D2 then copy it down it "fixes" the range you are looking in.

Does that make sense?

Almost but not quite.

The lookup range the way I showed you fixes the rows between 2 and 382 (which is what you currently have on your PURGE sheet.

There are a couple of ways you can change this to make it grow as you add to your PURGE sheet.

1 - (and this is my preference) - change the data on the PURGE sheet to a table like you have on your STORE FILL sheet. You can then modify the original formula to look at the table column instead of a fixed range. So if your table on the PURGE page was Table2 it might look like:

=VLOOKUP(D8,Table2[[LookupStore]:[CUSTOMER ]],2,FALSE) in Cell E8

2 - you can make the end row for your PURGE sheet variable. There are all sorts of ways of doing this, but this is one:

=VLOOKUP(D7,OFFSET(PURGE!$D$2,0,0,COUNTA(PURGE!$D:$D)-1,2),2,FALSE) in Cell E7, then copy to rest of column

This uses the OFFSET to work out where the last non-blank entry is in column D. The reason I prefer the first method is that this one can cause problems if you've got blank cells in column D on your PURGE sheet whereas the first one won't care.

Awesome, I get it.
I was doing the CTRL SHIFT ENTR, and it was annoying.
Thanks.

Item #1 is closed out! Yeah!

Originally Posted by FDibbins

A few question/observations...

There is no need to enter a formula like this...
=IF(VLOOKUP(I2,PURGE!I2:S1000,4,FALSE)=0,"",(VLOOKUP(I2,PURGE!I2:S1000,4,FALSE)))
as an ARRAY - that is when you use CTRL SHIFT ENTER to enter it and get it wrapped in {}. Array's are resource-intense and can start to slow your file down
1. $ is used to absolute a cell reference, locking it so that when copied, it doesnt change.. it comes in 3 different "flavors...

$A$1 absolutes the entire cell reference, it will not chance when copied down or across
$A1 absolutes only the column, "A" will not change when copied across, but "1" WILL change when copied down
A$1 absolutes only the row, "1" will not change when copied down, but "A" will change when copied across

So formulas like
=VLOOKUP(I2,PURGE!I2:S1000,2,FALSE)
need to be
=VLOOKUP($I2,PURGE!$I$2:$S$1000,2,FALSE)
otherwise, as you see, they change to this, when copied down...
=VLOOKUP(I3,PURGE!I3:S1001,2,FALSE)

Well that's one sorted

I think if you apply similar principles for #2 and #4 you might solve some of the things you need to do. As Ford says, these don't need to be array formulas so you can change those as well.

For #3 I'm not sure exactly which sheet you want to clear when you save as a new workbook. Does each user have their own separate "PURGE" sheets? If that's the case, if they copy their own data over the existing PURGE sheet, providing you have the formulas set up correctly on your STORE FILL sheet they will take care of this. If you want to automate this, it will need some VBA.

For #5 do you mean that you want to be able to see the contents of the cells even if they go beyond the cell edge? Using Wrap Text would achieve this, but I'm not sure I've really understood what you mean. I noticed that you add a lot of blank spaces to the end of your formula in column J on the COPY AND PASTE sheet and don't understand why.

Thank you. I get that, for #3, and that should then be taken care of.
For #5. Each special order is received in an adobe .pdf scan. I need to verify that information and log it (the spreadsheet).
I put the information into the "Store Fill" sheet. Then I copy from the corresponding row of the "Copy and Paste" sheet and put it into the .pdf sticky note.
What I'm trying to do, instead of having enough spaces to start another line " ", I would like to be able to act as though I'm pressing enter, like this.
There, another line is started.

So basically you want to embed a "line feed" inside your formula?

I'm not actually sure if that's even possible - I've never tried it. I'd be interested to know if any forum gurus have a solution though.

Superb, Tony!!! That's the ticket!

Originally Posted by Tony Valko

="Try"&CHAR(10)&"this"

Format the cell to wrap text.

CHAR(10) is a line feed character.

You may also have to adjust the row height.

Thank you, all, so far.
Here is an updated spreadsheet, with many of the recommended changes.

I've been trying to learn Excel on the fly. I'm running the special orders program for a small business. I'm hoping that VLOOKUP, If/Then and other functions can help locate information previously entered manually.
Based upon an item's UPC, I should be able to pull all specific information for the item, including size and color if applicable. Sometimes the UPC has not been entered previously, so I would like to use the Retail Listing Service (RLS) number to locate some of the information. I then use this information (in the Copy and Paste sheet) to copy then paste on to a pdf sticky note to print if necessary.
The data I use comes from the Purge sheet.
I'm not too concerned about populating data in columns D-G, as it's only about 4% of customers that are repeat in special orders.
Here's what I'm trying to do, and after weeks of reading and looking at tutorials online, cannot figure it out
1. SOLVED
2. If data is available for column I in the Purge sheet, columns J-S will populate. If there is not information (blank) or "various" in column I in the Purge sheet, when I enter RLS data in column L, I would like information in columns M-S to populate.
3. SOLVED
4. Least importantly, I don't use any more than last names for customers, but if columns D & E match, F & G will populate.
Again, I'm sorry If this is so rookie, but I'm trying.
5. SOLVED

Here is with Tony's update for the "CHAR(10)" acting as enter to start a new line.

Thank you for your help, Shirley!!!

Originally Posted by shirleyxls

So basically you want to embed a "line feed" inside your formula?

I'm not actually sure if that's even possible - I've never tried it. I'd be interested to know if any forum gurus have a solution though.

Glad to have helped a little

And thanks Tony for the Line Feed tip... another one to add to the armoury