Double Lookup Function

[wikiwinn]

hi, could someone help me?

I'm writing the vlookup function.
I want to double lookup.
it means,

If I type "20" in column A, the correpsonding number "10" will be displayed in Column B.
It is OK for me.

I also want to display "B" in column C and "1113" in column D, which are corresponded with "10" in Column B.
I'm facing this problem.
Have a look to excel file.

Thanks in advanced.

[vandanavai]

Hello

see formulas in green coloured cells in attached file.

[endz0926]

Hello am a new member allow me to read conversation in this thread.
Thank you.
Endz0926

[martindwilson]

deleted mis-understood question(as usual!).

[wikiwinn]

Dear vandanavai,
Thanks for your quick reply.
May be, my question is not completed. It is my fault.
I want to enter the number randomly, i.e., if I enter the number in column A, I will not enter the column B and if I enter the number in column B, I will not enter the column A.
My need is
if I enter number in Sheet 1's Column A, the sheet 1's column B, C & D will automatically displayed according to Data in Sheet 2.
If I enter number in Sheet 1's Column B, the Sheet 1's column A, C & D will automatically displayed according to Data in Sheet 2.

Could you please help me.
Thanks in advanced.
Wikiwinn

[martindwilson]

will sheet 2 column A values EVER be the same anywhere in coumn B?

[wikiwinn]

will sheet 2 column A values EVER be the same anywhere in coumn B?

Yes,
In sheet 2, data will like this
colA colB colC colD
1 2 AA 1112
2 5 BB 1113
3 7 CC 1114 etc.

In sheet 1, I will randomly type the number, either at ColA or ColB.
if i type 1 in colA, the data 2,AA,1112 will automatically displayed at ColB,ColC, ColD.
if i type 5 in colB, the data 2,BB,1113 will automatically displayed at ColA,ColC, ColD.

Thanks in advanced.

[martindwilson]

you cant do that, you would have to overtype the formula
what you can do is use a couple of other cells to enter your search in like this
see attached