silverwolf82587

Hello,

This what I'm doing.

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Or

=VLOOKUP(Sequioa!A:A,MBS!A:A,1,FALSE)


There are 3 worksheets:

1. Sequoia
2. MBS
3. Result

I am trying to compare sequoia worksheet’s values to that of MBS, if it exists in both I want it to print that value or entire in the result worksheet.
Also I need to do this for each value (each row), create a loop somehow.


Thank you,

Your help is greatly appreciated.

Wes

Your help is greatly appreciated.

Wes

gistek

=VLOOKUP(Sequoia!A1,MBS!A:A,1,FALSE)

Will show the values for items in Sequoia and also in MBS with an #N/A for items in Sequoia and not MBS.

=VLOOKUP(MBS!A1,Sequoia!A:A,1,FALSE)

Will show the values for items in MBS and also in Sequoia with an "N/A for items in MSB and not in Sequoia.

silverwolf82587

Sorry for my ignorance. Do they make a where command?

I just need it to spit out the entry on the third worksheet, if the entry exists in both the first two worksheets.

Thanks again.

gistek

If the data are numeric, and positive, you can use
=IF(VLOOKUP(Sequoia!A1,MBS!A:A,1,FALSE)>0,Sequoia!A1," ")

This should give you the value for the matching entries without the "N/A" where the entries don't match.

If I get a chance I'll experiment with this and see if I can find a better solution.

You might end up needing a macro, though and I haven't done much macro work yet.

silverwolf82587

=VLOOKUP(Sequioa!A1,MBS!A:A,1,FALSE)
In english to me this means compare sequioa A1's value to that of mbs's entire a column, if there is an exact match found spit out that value on result worksheet, if not, spit out N/A.

Is this correct? I click and drag the formula down to other cells and it basically does what i wanted. certain cells spit out data others say n/a.

thanks again.

gistek

Yep. That's what it does.

silverwolf82587

hey is there a way to take the result with all of the numeric values and put them on another worksheet in the same column in different rows. that way i have several hundred rows rather than 20000.


thanks again.

gistek

You might want to look into migrating to Access for data sets that large.You can still use your Excel spreadsheets as base tables by linking them into Access, but you would do your comparisons and generate a results table with only the results (none of those N/A's in it). This could then be output to Excel if you need the results there.