Find "by VLOOKUP" from the right

[shimaa01234]

Find "by VLOOKUP" from the "LEFT"
I do not want to use any other formula
I want to use" VLOOKUP" formula

[oeldere]

If you want to do it with VLookup you have to re-arange your data.

VLookup can not find a value on the left side of the table.

See the attached file, with the VLookup formula.

As stated I changed the format.

If you don't want to change the format, you need to rely on index/match.

[Ron Coderre]

VLOOKUP only works from left to right. Consequently, you need to work *with* Excel, instead of *against* it.
The best approach to lookup the ID (an the others, frankly) is to use an INDEX/MATCH combination. It's faster and more flexible.

Please Login or Register  to view this content.

And the "School Year" and "fail / succee" would be done this way.

Please Login or Register  to view this content.

I hope that helps.

[shimaa01234]

I am very sorry Mister "Oeldere"
I want to use without changing columns

[Tony Valko]

Like this...

=VLOOKUP(J5,CHOOSE({1,2},D6:D12,C6:C12),2,0)

[oeldere]

Then rely on the solutions in #3.

[shimaa01234]

Yes Mister "Tony"
This is exactly the desired
Are there other ways and other formats can be used

[shimaa01234]

Unfortunately Mister "Oeldere"
I do not want to transfer any columns

[Tony Valko]

Depending on what version of Excel you're using, I can think of a couple other ways to do this but they are not the BEST solutions for this type of application.

KISS - Keep It Simple Stupid!

[shimaa01234]

Unfortunately Mister "Tony" also did not work with the rest of the columns
I use Office 2003

[Tony Valko]

The other columns would use a normal VLOOKUP (left to right).

However, if you use the INDEX/MATCH, as others have suggested, you can enter a single formula and just drag copy it.

Using VLOOKUP requires 2 different formulas.

[shimaa01234]

I want a function "vlookup"
Looking in from the left, you can add them any other formula, you search for such a function, "index" with "match"

[oeldere]

Please Login or Register  to view this content.

The statement above from #10 (is not clear to me.)

you have received reply's from several forummembers.

In that case it is usefull to add to who you are replying.

[oeldere]

please reply on my request in #13.

[shimaa01234]

I am very sorry Mister "Oilder"
your idea is great, but I want you solve formulas without moving columns

[protonLeah]

Please don't insult members (#14) by deliberately mis-spelling their names (#16).
Post reported.

[FDibbins]

You dont want to move/arrange columns, you dont want to use a (complex but) proven formula (post #5) and you dont want to use the suggested Index/Match function. Unfortunately, the "magic wand" forum has magic'd itself away for a vacation, so I'm not sure how much more we can help

[shimaa01234]

I am very sorry
I did not mean any insult, of course, but it's just a mistake in writing

[shimaa01234]

I "Arabic woman" so it can be a mistake in the translation
Unfortunately again

[shimaa01234]

Thank you very much Mr. "FDibbins"
This answer, simple and wonderful

[FDibbins]

Intentions are sometimes lost in translation, and your explanation and apology, im sure, will be accepted by all

Having said that, apart from the suggestions already made, Im not sure what else we can offer to help you. Your insistence on using VLOOKUP (is this homework), when there are other, relatively simple, options open to you that will, I think, give you want you want, is puzzling to me

[AlKey]

Here is another variation of the LEFT(Reverse) lookup

=VLOOKUP(J5,IF({1,0,0},$D$6:$D$12,$C$6:$C$12),2,0)

[Haseeb Avarakkan]

Hello Shimaa,

For ID you can use any formula suggested by Tony (Post#5) or AlKey (Post#22)

For School Year & Fail/Success, you can use normal VLOOKUP

ID: =VLOOKUP(J5,IF({1,0},D6:D12,C6:C12),2,0)

School Year: =VLOOKUP(J5,D6:F12,2,0)

Fail / Success: =VLOOKUP(J5,D6:F12,3,0)

[alansidman]

Look at the attached

[AlKey]

I don't understand why does it have to be VLOOKUP only! There are ways of getting it done with one formula.

In J6 and copy down.

=INDEX($C$6:$F$12,MATCH(J$5,$D$6:$D$12,0),LOOKUP(ROWS(J$5:J5),{1,1;2,3;3,4}))

	C	D	E	F	G	H	I	J
5	ID	STUDENT NAME	School year	fail / succee			NAME	A
6	10034	A	1	succeed			ID	10034
7	10035	B	2	succeed			School year	1
8	10036	C	3	Fail			fail / succee	succeed
9	10037	D	4	succeed
10	10038	E	5	succeed
11	10039	F	6	succeed
12	10040	G	7	Fail

And Here is with VLOOKUP

In J6 and copy down.

=VLOOKUP($J$5,CHOOSE({1,2,3,4},$D$6:$D$12,$C$6:$C$12,$E$6:$E$12,$F$6:$F$12),ROWS(J$5:J5)+1,0)

[shimaa01234]

Thank you very much for all your generosity
And I'm sorry again for the misunderstanding
And very thankful you gave me a helping
Thank you very much Mr. "Oeldere"
Thank you very much Mr. "Ron Coderre"
Thank you very much Mr. "FDibbins"
Thank you very much Mr. "Alkey"
Thank you very much Mr. "Haseeb A"
Thank you very much Mr. "alansidman"
All solutions beautiful

[oeldere]

Please Login or Register  to view this content.

Since you been pointed out writing the correct name while refering, I wonder why you consist in wrong writing my name !!!

Edit:

Since you was on the forum, when I posted this comment, I expected you would reply.