Vlookup or index/match

[lumberjim]

I've done a little browsing of youtube tutorials, but I'm having a hard time isolating what I need.

I have a working spreadsheet that records the sales for each month and analyzes the data... but i would like to be able to 'take a picture' of a single day. I will want the data in a different format than it is currently entered, so I'm thinking vlookup might not work, since it only goes to the right.

Attached workbook has 2 sheets. 'display' is where I want the data displayed. field A1 is where I want to put any particular day of the month. I'd like it to go look on the 'info' tab for things in the corresponding fields based on a date match. the date is column C in my source sheet, but I want that in column A on the display sheet.
Can someone help me with the proper formula, and advise me on how to map them?

Please Login or Register  to view this content.

[ChemistB]

I'm guessing you will have more than one matching date (i.e. 4/9/2013 appears multiple times in Col C)? Is it in chronological order so they all appear together?

[Winon]

I also find myself a bit baffled with this issue.

[lumberjim]

yes, the date may appear several times, and will commonly be in non chronological order. I have further complicating factors like split deals to contend with, as well, but I may be able to noodle through once I get the proper formula here.

[judgeh59]

so here is some basic INDEX/MATCH formulas that will work...there is one for each cell...the problem is that the system doesn't know how many will match....so you really can't fill the the columns with formulas....with a little re-organization a pivot table would do great here....HTH

[ChemistB]

Here's an example that works if the dates are in chronological order (just for future reference)
The formula, copied down is like so
=IFERROR(INDEX(INDEX(Sheet2!$C$3:$C$14, MATCH($A$1,Sheet2!$C$3:$C$14,0)):INDEX(Sheet2!$C$3:$C$14, MATCH($A$1,Sheet2!$C$3:$C$14)),ROW(A1)),"")

[lumberjim]

I'm linking the actual sheet here. I had some help from this site in creating it, and it works great. I considered using the indexing function the way this thing splits the all deals tab down into team1, team2, etc, and then onto individual salesmen... but I've forgotten what fragile understanding I had of that back when we created it. If I could use that to index the fields by date...but be able to change the date... that might be what I need to use.

edit. the file is too large.

I'll link the old thread where it got created.

here it is

[Winon]

Since I don't favour PT's, I will gracefully bow out.

[ChemistB]

This solution is actually easier and doesn't require chronological order. You do need a helper/dummy column. You can hide this column later.
In first blank column of data sheet (D in my sheet), enter

=IF(C3=Sheet1!$A$1,D2+1,D2) copied down

In sheet 1 (for date in my example)
=IFERROR(INDEX(Sheet2!$C$3:$C$14,MATCH(ROW(A1),Sheet2!$D$3:$D$14,0)),"")

for results (in my example)
=IFERROR(INDEX(Sheet2!$B$3:$B$14,MATCH(ROW(A1),Sheet2!$D$3:$D$14,0)),"")

[ChemistB]

What's a PT, Philosophical Theory?

[lumberjim]

This solution is actually easier and doesn't require chronological order. You do need a helper/dummy column. You can hide this column later.
In first blank column of data sheet (D in my sheet), enter

=IF(C3=Sheet1!$A$1,D2+1,D2) copied down

In sheet 1 (for date in my example)
=IFERROR(INDEX(Sheet2!$C$3:$C$14,MATCH(ROW(A1),Sheet2!$D$3:$D$14,0)),"")

for results (in my example)
=IFERROR(INDEX(Sheet2!$B$3:$B$14,MATCH(ROW(A1),Sheet2!$D$3:$D$14,0)),"")

I'm trying to understand what's happening here. You're tagging each instance of a date in column D of sheet 2. then indexing them based on a match with A1 on sheet one.

I need to translate this into my sheet, and I have multiple fields I need to pull for each row with a matched date. the date in my data sheet is in column D. I've put the dummy column under S.

Should the A1 in your formula be a locked coord? or should it change? I'm not sure if that's referring to the date i want, or the row?

I'm confused.

I've deleted all other sheets in the workbook to make it small enough to upload. Could you take a peek and apply this solution to the sheets as I have them?

Please Login or Register  to view this content.

[lumberjim]

hmm.. I think I might have figure it out on my own. What does the 'row(a1)' part do. that change does not seem to affect much

[Winon]

Hi lumberjim,

hmm.. I think I might have figure it out on my own.

I think ChemistB came up with a good solution, and this is what I got, should you want to compare results.

[ChemistB]

Hey Jim

What does the 'row(a1)' part do. that change does not seem to affect much

As you copy your formula down the page, that value will increment (row(a2) =2, row(a3)=3 and so on). Then the Match formula is looking for the second instance of your date, the 3rd instance, etc. Does that make sense?

[lumberjim]

Yes, and it's working like magic. ( as in I have no real grok)


So the formula on the 2nd sheet creates a 1 IF the date matches the one entered on sheet one. I get that. and it adds one for each subsequent instance because of how you've 'stacked' the formula. so if I have 11 deals on 4/1, and I give it that date to hunt.... I get a 1 on the first row, a 2 in the second and so on down to 11.... but then all the rows below that last 11 are also 11. and it does NOT pull them?

I don't get that part.

[ChemistB]

INDEX(Sheet2!$B$3:$B$14,MATCH(ROW(A1),Sheet2!$D$3:$D$14,0))
An INDEX/MATCH will only ever return a single match. When you use the 0 in the MATCH function, it returns the first exact match it finds. It will ignore all others. That's why we needed to use that dummy column in the first place. If we'd just asked to Match the date, it would have only returned the first match.

Hope that helps.

[lumberjim]

yes it does, actually. What are the alternatives to the 0? 1 and blank? 1 and 2? and what would it do if that was used?

[ChemistB]

They are 1 (or if you leave it out, it defaults to 1) and -1.

For both of these, the values should be in order. For 1, from lowest to highest, for -1 from highest to lowest.

These are used typically in table ranges (like a tax table or rainfall table)
1 looks for last value less than or equal to the value. So with values such as 10, 30, 70, 100
MATCH(35, A1:A4,1) or MATCH(35, A1:A4) would return a 2.
MATCH(0, A1:A4,1) will give an error (there is no value less than or equal to 0)
MATCH(35, A1:A4, 0) will give error as there is no exact match
MATCH(35, A1:A4, -1) will give error as the values are in the wrong order

Hope that helps.

[lumberjim]

Makes sense.

Hey, I'm doing another page like this but using the salesman's name as the index key instead of the date. I've adapted that same pair of formulas and it's working. However, I have a couple of simple addition calculations in columns adjacent that give me totals of certain other columns ...but if those rows are blank, I get #VALUE!. Is there a way to avoid that happening? It's not a big deal... just that i want to print this page to motivate, and it looks like there's an error there.

[lumberjim]

nevermind. Not elegant, but I just put an IF statement in there to give me a blank box if there's no name on that row. seems to work

[ChemistB]

Sometimes an IF statement is the best way to go. Glad you got it working.

[lumberjim]

I figured out how to use the IFERROR from your formula after I did all that. same effect I guess... but yours is slicker.

Thanks a lot for your help with this, Chemist. My boss is impressed. I gave this place mad props.

[lumberjim]

OK,
I'm bumping this thread to ask for a little more help with the same sheet.

If I want this to pull a row if it hits a match in either of 2 columns, how would you do that?

I'm using

Please Login or Register  to view this content.

in a hidden column on the worksheet where the data is entered on DEALS. F9 is where the salesman's name goes, so on SALESMAN where I can enter that guy's name in D1 I have this:

Please Login or Register  to view this content.

works great. but some deals are split, so I have another column, (G) where the 2nd guy goes, and another hidden column with

Please Login or Register  to view this content.

in it.

can I incorporate an OR somehow, so that it will list the deals if it matches the name in d1 in EITHER column F OR G ??

[Winon]

I don't understand exactly on which sheet you want to do what.

Could you perhaps be looking for something like this?

Please Login or Register  to view this content.

If I am wrong, could you please upload a sample WorkBook with how it is setup, and a before and after example?

[lumberjim]

NEW DEAL LOG 4-23-13.xlsx

no, that formula results in both records being included in one cell. see SALESMAN tab cells c25, d25.

what this workbook does:

you enter the deal info into the 'deals' sheet.
I have another sheet for pulling an inquiry per salesman. That sheet is set up to look at the deals sheet, and return any row with the selected salesman's name in it. It then indexes them and provides totals, averages, summary info etc...

the tricky bit is that they sometimes split deals. So there can be 2 salesmen on one deal. I need to be able to include a row if the deals sheet has a match in EITHER column F or G. I need them on separate rows of the SALESMAN sheet.

thanks!

jim

[ChemistB]

Unless you need to separate Salesman 1 from salesman 2 somewhere else. You only need either Column V or Column W and combine the formulas

=IF(OR(SALESMAN!$D$1=F9,SALESMAN!$D$1=G9),V8+1,V8)

If you do need them separated, you could insert another column and
=V9+W9 and reference that column.
Does that help?

[lumberjim]

perfect. simple. perfect.


thanks again, chemist. you're my hero.

[khalil-UK]

i have a problem and not sure where to go!!!!
i have a timeline on column A 6:30am 6:45am 7:00am and so on. i also have a number of classes that show up in column H with a start time in column i and finish time in column J. In column B is what i would like to have in an ideal world, now saying that what i want is in column C a conditional format that shows a shading from e.g 7:00am-8:00am for a certain class
please help

Condtional format test 1.xlsx

[arlu1201]

khalil,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.