VLOOKUP with Column Letter

[Nanogirl21]

Is there any w/ay to write a VLOOKUP that will return the value in a specific column that is identified by a letter instead of the number of cells from a refrence point?

I hope my quiestion makes sense. I had a diffifuclt time trying to come up with the correct words

EXAMPLE:

CURRENT VLOOKUP:

=VLOOKUP(B16,Apples!B:Z,[B]17[/B],FALSE)

WANTED RESULTS SOMETHING LIKE:

=VLOOKUP(B16,Apples!B:Z,Column R,FALSE)

 

[Aladin Akyurek]

Column R's numeric position is 18, not 17.

Do you have header values in row 1 of Apples!B:Z? If so, what do you have in R1?

 

[mattstopel]

Have you considered using an index and match?

I assume you want to look up based on B:B in Apples worksheet?

=index(apples!R:R,match(B16,apples!B:B,0))

If you wanted to build you could name the range (Highlight the R:R data and name it what ever you like(top left hand corner, I have named it "example")) and the B Range Data "ID"

=INDEX(example,MATCH(B16,ID,0))

This may make referring to it later much easier for you.

 

[Tetra201]

...WANTED RESULTS SOMETHING LIKE:

=VLOOKUP(B16,Apples!B:Z,Column R,FALSE)

Not exactly what you wanted, but close enough:

=VLOOKUP(B16,Apples!B:Z,COLUMNS(B:R),FALSE)

 

[Marcelo Branco]

Maybe...

=VLOOKUP(B16,Apples!B:Z,SEARCH(C16,"BCDEFGHIJKLMNOPQRSTUVWXYZ"),0)
where C16 contains the desired column - for example, R

M.

 

[Joyner]

Hello,

To All: was there a VLOOKUP UDF written to do this? I seems to recall something like that but can't find it yet in my search. But I may be thinking of the VLOOKUP with -X number of columns for the return value

Thanks

 

[Aladin Akyurek]

@ Nanogirl21

You can also switch to a formula with MATCH using INDEX...

=INDEX(Apples!R:R,MATCH(B16,Apples!B:B,0))

 

[Nanogirl21]

Column R's numeric position is 18, not 17.

Do you have header values in row 1 of Apples!B:Z? If so, what do you have in R1?

Yes, should be 18 not 17. That was a mistake.

I do have headers. The headers in all columns of the first row. R1 is called Comments.

 

[Nanogirl21]

Not exactly what you wanted, but close enough:

=VLOOKUP(B16,Apples!B:Z,COLUMNS(B:R),FALSE)

How does this version know what column to look in? Is it always the last column refrencd in the range? If I want to use the same formula but return the value of column G would I use COLUMNS(B:G)?

 

[Nanogirl21]

@ Nanogirl21

You can also switch to a formula with MATCH using INDEX...

=INDEX(Apples!R:R,MATCH(B16,Apples!B:B,0))

Have you considered using an index and match?

I assume you want to look up based on B:B in Apples worksheet?

=index(apples!R:R,match(B16,apples!B:B,0))

If you wanted to build you could name the range (Highlight the R:R data and name it what ever you like(top left hand corner, I have named it "example")) and the B Range Data "ID"

=INDEX(example,MATCH(B16,ID,0))

This may make referring to it later much easier for you.

I am not really familiar with the index and match process and how it exactly works. I think it would be too complicated for me to learn it with all that I have going on at the moment.