1

I am not good with the advanced Excel stuff, and I've got something which appears to be a VLOOKUP question (which I have never used before).

The situation is such. I need to search for a specific value in six columns of a sheet and return the value of the cell adjacent to the right. So far I have a-googled many options, none of which work, although I did get ISERROR to work and tell me there's no match (which is wrong).

In fact, here is that one, which seems most promising:

=IF(ISERROR(MATCH(2013,I2:N433,0)), "No Match", VLOOKUP(2013,J2:N433,+1,FALSE))

Here's is what I think it is doing:

  • The IF gets it rolling
  • ISERROR is returning "No match" if the criteria are not met...
  • ...which should be the MATCH section, searching for 2013 in the range J2:N433, 0 for exact
  • ...and if the criteria are met, then the VLOOKUP is looking for 2013 in the array J2:N433
  • ...putting in the +1 to return the value of one column over
  • ...and FALSE used for an exact match

So I think the +1 is a sticking point. I read a post on here or excelforum.com saying that when using a MATCH, +1 increments one column, but that just seems too loose for Excel.

To be honest, I have read a lot of posts over the past two hours, and they're all jumbled in my head. I figured I'd just head right to here and see who can un-jumble my ideas.

Thanks very much. I am using Mac Excel 2011.

EDIT: Here is a chunk of my data.

2011    6   2012    5   2013    6   2014    57
2011    6   2012    5   2013    6   2014    57
2011    11  2012    10  2013    11  2014    62
2010    11  2011    10  2012    11  2013    62
2010    11  2011    10  2012    11  2013    62

What I wish to see is be able to make another column that finds the 2013s then displays the value to their right, such as 6, 11, 62, etc.

EDIT 2: I added the above data upon Werner's request as you don't get new lines in comments. When I did that, I apparently lost the answer thread...? Annoying. Sorry, I didn't think that would happen.

laerm
  • 27
  • 1
  • 1
  • 4
  • Is there only one occurrence of the value in the range? And is the adjacent value text or a number? – Andy G Aug 12 '13 at 00:48
  • There are multiple occurrences of the value and the adjacent value is a number. – laerm Aug 12 '13 at 04:08
  • It would be great if you could supply some sort of example input and the expected output. It'll provide a clearer picture of what you're after. – Werner Aug 12 '13 at 04:42
  • 1
    I believe vlookup only searches the left most column – Pynner Aug 12 '13 at 11:05
  • 1
    How do you want the results displayed? Your question just says: 6, 11, 62, etc. Does that mean you want all the values in one cell in a comma-separated list? Do you want the value that you are searching for displayed anyplace? Please clarify what you want. – Ron Rosenfeld Sep 09 '14 at 01:08

2 Answers2

0

Based on Excel 2007 (so beware!), first I'd suggest copying your data (6 or 8 columns, I'm not sure - but it does not really matter) and pasting it into Word as unformatted text. Select, Insert > Tables - Table, Convert Text to Table with Number of columns: 2 and Separate text at Tabs, OK. Hopefully you can copy this back into Excel (say A2) and have a more ordered data set.

Add column labels (say Year and Value) and create a PivotTable (say in D2) from ColumnA:B with Value for Column Labels, Year for Row Labels and Value in the Values field. Somewhere off to the right (depending upon how many unique values there are in your data set) put in Row4: 

=IF(ISBLANK(E4),"",E$3)

Copy across and down to suit.

Based on the sample, Data, Data Tools - Remove Duplicates might help to weed out some repetition and it might be viable to insert cells manually to align the columns by year before extracting unique values for each column in turn (Data > Sort & Filter - Advanced, Copy to another location/Unique records only)..

pnuts
  • 58,317
  • 11
  • 87
  • 139
  • Hmmm...it seems like there'd be a way to do this more programmatically than using this method. If I wanted to, I could manually align the cells by year to make columns of the same year now. Then I wouldn't have to do anything other than grab a whole column. I'm trying to avoid that and teach myself some new techniques in the process. – laerm Aug 13 '13 at 14:19
0

Possibly a way to do this more programmatically:

Label your columns in pairs, say A,B A,B etc. Create a PivotTable from multiple consolidation ranges, eg as shown in detail here except that at Step 2b of 3 add the range appropriate for each pair of columns separately. Having created the PT, click on the row total for 2013 (rather than the GT intercept). Delete ColumnB from the resulting Table.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139