Vlookup or vba or code

[Vishaal]

Thanks in advance

I am using the following vlookup

Lookup value = C10
Table array =sheet1!$B$2:$GKX$5
Col index = 5041
Range lookup=0

Now problem is
I want to change the table array "sheet1!$B$2:$GKX$5" for coloumn from B to GKW

And also change the coloumn index accordingly, like 5040, 5039

 

[mohadin]

Any way this is how I figured out to solve you Q

=VLOOKUP(A3;INDIRECT("sheet1!"&ADDRESS(2;COLUMN()-1)&":"&"$D$5");COLUMN()-6;0)

Have a good play with

 

[Vishaal]

Hi,

pls find the sheets

First sheet where we have the vlookup

	Col A	Col B	Col C	Col D	Col E
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9	200
Row 10	420
Row 11	510
Row 12	605
Row 13	709
Row 14	804
Row 15	901
Row 16	105
Row 17	206
Row 18	303

<colgroup><col width="64" span="6" style="width:48pt"> </colgroup><tbody>
</tbody>

Second Sheet

205	150	520	205	520	205	150	Yes
403	140	201	403	201	403	140	Yes
150	160	360	150	360	150	160	Yes

<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>
</tbody>

need this result

	Col A	Col B	Col C	Col D	Col E	Col F	Col G	Col H	Col I
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6
Row 7
Row 8
Row 9	200				#N/A	#N/A	#N/A	#N/A	#N/A
Row 10	420				#N/A	#N/A	#N/A	#N/A	#N/A
Row 11	510				#N/A	#N/A	#N/A	#N/A	#N/A
Row 12	605				#N/A	#N/A	#N/A	#N/A	#N/A
Row 13	709				#N/A	#N/A	#N/A	#N/A	#N/A

<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>
</tbody>

Column E
Vlookup

=VLOOKUP(B10,[Book2]Sheet1!$B$2:$8,0)

=VLOOKUP(B11,[Book2]Sheet1!$B$2:$8,0)

=VLOOKUP(B12,[Book2]Sheet1!$B$2:$8,0)

=VLOOKUP(B13,[Book2]Sheet1!$B$2:$8,0)

=VLOOKUP(B14,[Book2]Sheet1!$B$2:$8,0) Coloumn F vlookup =VLOOKUP(B10,[Book2]Sheet1!$C$2:$7,0) =VLOOKUP(B11,[Book2]Sheet1!$C$2:$7,0) =VLOOKUP(B12,[Book2]Sheet1!$C$2:$7,0) =VLOOKUP(B13,[Book2]Sheet1!$C$2:$7,0) =VLOOKUP(B14,[Book2]Sheet1!$C$2:$7,0) <colgroup><col></colgroup><tbody> </tbody> Coloumn G vlookuo

<colgroup><col></colgroup><tbody>
</tbody>

=VLOOKUP(B10,[Book2]Sheet1!$D$2:$6,0)

=VLOOKUP(B11,[Book2]Sheet1!$D$2:$6,0)

=VLOOKUP(B12,[Book2]Sheet1!$D$2:$6,0)

=VLOOKUP(B13,[Book2]Sheet1!$D$2:$6,0)

=VLOOKUP(B14,[Book2]Sheet1!$D$2:$6,0)

<colgroup><col></colgroup><tbody>
</tbody>

pls check and update me

 

[Vishaal]

can we make it through VBA

 

[Vishaal]

hello

 

[Fluff]

Try something like

Sub Vishaal()
   Range("E2:G12").Formula = "=index(Sheet1!$GKW$2:$GKW$10,match($B2,Sheet1!B$2:B$12,0))"
End Sub

Also in future, please be patient, we are all volunteers giving what free time we have.

 

[Vishaal]

Hi Fluff,

Tried but not working

 

[Vishaal]

You can check this sheet

Excel 2010 32 bit
	B	C	D	E	F	G	H	I	J	K
1
2							120	180	207	110
3							260	540	304	108
4							340	140	105	101
5							260	470	205	290
6							480	210	310	401
7
8
9
10		220
11		120
12		540
13		290
14		310
Sheet1

in this sheet we have taken all the data in one sheet

First Column C (Row10 ) Data will be check from Column H (Row 2 to 6) and if the value 220 match, show the value Yes or No in Column H (Row 10)
First Column C (Row11 ) Data will be check from Column H (Row 2 to 6) and if the value 120 match, show the value Yes or No in Column H (Row 11)
First Column C (Row12 ) Data will be check from Column H (Row 2 to 6) and if the value 540 match, show the value Yes or No in Column H (Row 12)
First Column C (Row13 ) Data will be check from Column H (Row 2 to 6) and if the value 290 match, show the value Yes or No in Column H (Row 13)
First Column C (Row14 ) Data will be check from Column H (Row 2 to 6) and if the value 310 match, show the value Yes or No in Column H (Row 14)

First Column C (Row10 ) Data will be check from Column I (Row 2 to 6) and if the value 220 match, show the value Yes or No in Coloumn I (Row 10)
First Column C (Row11 ) Data will be check from Column I (Row 2 to 6) and if the value 120 match, show the value Yes or No in Coloumn I (Row 11)
First Column C (Row12 ) Data will be check from Column I (Row 2 to 6) and if the value 540 match, show the value Yes or No in Coloumn I (Row 12)
First Column C (Row13 ) Data will be check from Column I (Row 2 to 6) and if the value 290 match, show the value Yes or No in Coloumn I (Row 13)
First Column C (Row14 ) Data will be check from Column I (Row 2 to 6) and if the value 310 match, show the value Yes or No in Coloumn I (Row 14)

First Column C (Row10 ) Data will be check from Column J (Row 2 to 6) and if the value 220 match, show the value Yes or No in Coloumn J (Row 10)
First Column C (Row11 ) Data will be check from Column J (Row 2 to 6) and if the value 120 match, show the value Yes or No in Coloumn J (Row 11)
First Column C (Row12 ) Data will be check from Column J (Row 2 to 6) and if the value 540 match, show the value Yes or No in Coloumn J (Row 12)
First Column C (Row13 ) Data will be check from Column J (Row 2 to 6) and if the value 290 match, show the value Yes or No in Coloumn J (Row 13)
First Column C (Row14 ) Data will be check from Column J (Row 2 to 6) and if the value 310 match, show the value Yes or No in Coloumn J (Row 14)

Help Please

 

[mohadin]

Try and tell me PLS
Cell H11=IFNA(VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-5));INDIRECT(ADDRESS(2;COLUMN())&":"&"$k$7");COLUMN()-7;0);"NO") And drag down
Cell I11=IFNA(VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-6));INDIRECT(ADDRESS(2;COLUMN())&":"&"$k$7");COLUMN()-8;0);"NO") And drag down
Cell j11=IFNA(VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-7));INDIRECT(ADDRESS(2;COLUMN())&":"&"$k$7");COLUMN()-9;0);"NO") And drag down
cell k11=IFNA(VLOOKUP(INDIRECT(ADDRESS(ROW();COLUMN()-8));INDIRECT(ADDRESS(2;COLUMN())&":"&"$k$7");COLUMN()-10;0);"NO") And drag down



At least almost?

 

[mohadin]

Ps
H10,I10,J10,K10

 

[Vishaal]

i have tried but ni

pls find the sample file url

i dont know its allowed or not

https://app.box.com/s/2tafa5ag1jmr1rvhtawt87hirc55n2c3