Vlookup Technique Help

[Excel777]

Hey,
Plz Need Help In This Lookup Technique

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2	Formula	LookUp Values	Table Array		Data	Results		Table1		Table2		Table3
3	#N/A	A1:A9	Table2		A1	100		A1	1	A1	100	A1	1000
4					A2	101		A2	2	A2	101	A2	1001
5					A3	102		A3	3	A3	102	A3	1002
6					A4	103		A4	4	A4	103	A4	1003
7					A5	104		A5	5	A5	104	A5	1004
8					A6	105		A6	6	A6	105	A6	1005
9					A7	106		A7	7	A7	106	A7	1006
10					A8	107		A8	8	A8	107	A8	1007
11					A9	108		A9	9	A9	108	A9	1008
12								A10	10	A10	109	A10	1009
13								A11	11	A11	110	A11	1010
14								A12	12	A12	111	A12	1011
15								A13	13	A13	112	A13	1012
16								A14	14	A14	113	A14	1013
17								A15	15	A15	114	A15	1014
Sheet2

My look Up Formula Is In Cell A3
In Cell B3 The "Lookup Values" It's The Range From "A1:A9"
In Cell C3 The Data List Including Table 1,2,3 "Table Array"
My Results Of Shoul Be In Cells F3:F11

How Can I Apply This

Thanks In Advance

 

[Grugeon]

Your vlookup formula will be in cells F3:F11, not in A3

I am assuming that Table2 is a named range.

In F# put

=VLookup(E1,Table2,2,false)

If Table2 is not a named range put

=VLookup(E1,$J$3:$J$17,2,false)

Then drag this formula down to F11.

Now ...

This will not change if you change the cells B3 and C3. If you want to do that you will need to use cells that contain addresses of single cells at B3 to B11 and a range at C3. Then you will need to use the indirect function for the addresses in col 2 and and an indirect absolute reference to $C$3.

 

[Misca]

VLOOKUP can only search one value at a time. You're going to need a vlookup for each of your lookup values.

Try

=VLOOKUP(B3,INDIRECT(C3),2,0)

 

[Excel777]

Thnks For All The Answer But I Want To Get Results Direct
In Column F With Writting Only One Formula In A3

B3 IS "A1:A9" This All Range

Not
A1
A2
A3
A4

 

[Grugeon]

Impossible unless you feel like doing the work of writing VBA to do it in which case you would not even need a formula in A3.

 

[Excel777]

Why Impossible, i Don't See This
When In look up values i look up each Range In Speceifc Table and it's more easy to Write lookup values in B3 "A1:A9" Than Modifying Formulas In Column F

Imagine You Deal with 40,000 Row

I Thin Expert Can Make VBa Function For It

 

[Domski]

Grugeon is right, without VBA you van't have results magically appear in cells without them containing formula.

Dom

 

[Excel777]

Yeah I Know So Need VBA Function Can Do This

 

[VoG]

Try this

Sub lkup()
Dim c As Range, i As Integer
i = 2
Range("E3:F1000").ClearContents
For Each c In Range(Range("B3").Value)
    i = i + 1
    Range("E" & i).Value = c.Address(False, False)
    Range("F" & i).Formula = "=VLOOKUP(""" & Range("E" & i).Value & """," & Range("C3").Value & ",2,false)"
Next c
End Sub

 

[Excel777]

Thanks A alot Peter This Is The Code I Need Exactly
But It's Need Little Modification To Corrrspond With My Work

A1:A9 Which In Column "E" This If Fixed Data "Names"

For Ex

Sheet2

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 65px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>E</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">3</td><td style="text-align: center;">George</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">4</td><td style="text-align: center;">Mark</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">5</td><td style="text-align: center;">Peter</td></tr><tr style="height: 18px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">6</td><td style="text-align: center;">Andro</td></tr></tbody></table>

So I Should Write In "B3" Range E3:E6 it Refers To The Names From George To Andro Si In Need To Lookup This Name And Put the Values In Column "F"
Thanks In Advance

Excel tables to the web >> Excel Jeanie HTML 4