creating arrays from non-neighbor columns 1

[DragonRe]

Hello, is there a way (without using VB) of making an array without a column in between? For example I want an array that is column A and column C but *not* column B.
I'm using this in conjunction with vlookup.

Thanks!

 

[SkipVought]

Hi,

You want to lookup in BOTH Column A & Column C?

Maybe 2 VLOOKUPS? Maybe some sample data would help clarify.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DragonRe]

Background: I'm making a spreadsheet to help people use Rikku's "Mix" ability in FFX (then I'll add more content to it).
I've already created functionality that takes two "ingredients" and tells you what the result is. I've done this with both VBA and build in excel functions: ie vlookup, as an exercise.
Now I want to go the other way, start with result, and then output what it takes to make that happen.
Vlookup works great when it works. It seems a pain to get it to function though. Something I've noticed, if my data doesn't start in the first column vlookup doesn't want to work, ex if I'm looking at A:C it works, if I'm looking at F:I with the same data = no.

With that in mind, instead of having multiple instances of the data on seperate spreadsheets, I'd like to be able to make my array out of columns that are not adjacent.
Sorry for the ramble, and thanks for the help.

 

[SkipVought]

if my data doesn't start in the first column vlookup doesn't want to work"

I rarely use VLOOKUP.

Rather use INDEX & MATCH. Use MATCH to lookup the row offset and then use that row offset value in the second argument of the INDEX function on the return range.
[tt]
=INDEX(A:A,MATCH(lookupvalue,B:B,0),1)
[/tt]
Lookup in B, Return value in A.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Separate sheets...

It's no big deal to do a lookup on data in another sheet. As you're entering the formula, point to the SHEET and then the RANGE. (I never TYPE IN range references!!!)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DragonRe]

Hopefully you'll read this Skip, I just got index/match working. It's much more elegant and less magic than vlookup.
My original question is still not answered, but even better you gave me the solution to the root issue.

 

[SkipVought]

My original question is still not answered"

What is the remaining issue?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DragonRe]

Oh, well, my question was: how can you make an array out of non-neighbor columns
For example, I'm using index/match to look at columns A B and D, but I have to specify "A" which includes C. I don't believe this is causing any inefficiency because it's not actually having to search through C, I just don't like it.
But i wasn't complaining about the help you've provided.

If you feel like another nudge, how would you recommend I approach the next step I'm taking.
There are typically multiple matches, this is always finding just the first one. My thought is the only way to do this and not have it look sloppy is use VBA. It seems like I'll need to store variables to alter my search field, if statements, and I won't know how many cells I'll want to output to because there could be a variable amount of matches.

 

[SkipVought]

DragonRe,

Since this is really a different question, please post in a new thread. Include an answer to the issue stated below, in your new thread.

"There are typically multiple matches, this is always finding just the first one."

Please explain how your data is organized as far as SORT ORDER of the SEARCH range.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}