Solved VLOOKUP How 2 popul8 with dat frm other sheet

Microsoft Microsoft office excel 2007 ac...
July 4, 2011 at 07:37:42
Specs: Windows 7
I have Sheet2 with bulk data and Sheet1 presenting it (using drop down lists on Sheet1).

Drop Down Data on A2 of Sheet1 pulling from Column A on Sheet2:
Green Campus
Red Campus
Blue Campus
... and so on.

Bulk data on Sheet2:
A1=Green Campus
A2=Red Campus
A3=Blue Campus
... and so on.

B1=Address GC
B2=Address RC
B3=Address BC
... and so on.

C1= Text GC
C2= Text RC
C3= Text BC
... and so on.

How do I vlookup so that when I have "Green Campus" showing on A2 of Sheet1, the cells A3 and A4 on Sheet1 are populated automatically with info from Sheet2.

For example:

If A2 of Sheet1 shows what's on A1 of Sheet2 (using a dropdown list), then A3 automatically shows the data located at B1 of Sheet2 and A4 automatically shows the data located at C1 of Sheet2.

This is heavy duty excel.


See More: VLOOKUP How 2 popul8 with dat frm other sheet

Report •


#1
July 4, 2011 at 09:26:21
✔ Best Answer
re: "This is heavy duty excel."

Not really. VLOOKUP is a function that is used quite often and pretty easy to understand.

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

lookup_value: The value you want to look up

table_array: The range of cells where the lookup_value can be found in the first column of the range and the other columns contain the data you want to return

col_index_num: The column number of the table_array from which you want to return a value. Note: This may or may not be the same column number as the spreadsheet column. It is the column number within the table_array. e.g. with a table_array of B1:D100, Column B is col_index_num 1, Column C is 2, etc.

range_lookup: Either TRUE or FALSE (or 1 or 0) depending on whether you want an exact match or an approximate match of the lookup_value.

So, in your case, you would use something like this in A3:

=VLOOKUP(A2, Sheet2!$A$1:$C$100, 2, 0)

I'll leave it to you to figure out what goes in A4.

Note: If the lookup_value is not found in the first column of the table_array, VLOOKUP will return #N/A.

Since you are using a Data Validation drop down, this should not be an issue for you, but if it is, you can check for a #N/A value and present a "message" instead:

=IF(ISNA(VLOOKUP(A2, Sheet2!$A$1:$C$100, 2, 0)), "Value Not Found", VLOOKUP(A2, Sheet2!$A$1:$C$100, 2, 0))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
July 4, 2011 at 10:02:04
Not working.

Report •

#3
July 4, 2011 at 10:14:21
I know why is not working!

The lookup_value is GREEN CAMPUS T.S.U. (no space before G and no space after U.)

I think inbetween spaces and periods are making the formula give an error.

The thing is that I can't present GREENCAMPUSTSU, it's not user friendly to the eyes.

See now why I say this is heavy duty?

How can I make GREEN CAMPUS T.S.U. not give an error?


Report •

Related Solutions

#4
July 4, 2011 at 16:20:15
re: "See now why I say this is heavy duty?"

I don't want to get into a discussion as to the relative meaning of "heavy duty Excel", but trust me, nothing you have presented so far would be considered as "heavy duty" by regular users of Excel.

re: "Not working" and "making the formula give an error".

Keep in mind that we can not see your spreadsheet from where we're sitting. It's kind of hard (maybe even "heavy duty" hard) for us to know what you mean by "not working" or what you mean by "making the formula give an error".

re: "I think inbetween spaces and periods are making the formula give an error."

There is no reason for the spaces or periods to result in an error as long as the same spaces and periods are used in the first column of the table_array.

Trust me on that. Even though I didn't have to, I tested it by copying GREEN CAMPUS T.S.U. from your post and pasting it into both Sheet1!A2 and Sheet2!A1.

The formula I offered returned the value in Sheet2!B1 as expected. As long as an exact match for the lookup_value can be found in the first column of the table_array, VLOOKUP will find it.

Since you didn't tell us what "not working" means or what you mean by "making the formula give an error", I'm not going to speculate as to why you are having problems.

You'll need to provide some details as to the error. It might also help if you posted some sample data so that we can see what we're working with.

If you are going to post data, please click on the blue sentence at the end of this post and read the instructions on how to post data in this forum.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •


Ask Question