How to do Vlookup using VBA to sheet which is filtered?

[dmadhup]

Hi,

I am doing vlookup to filtered sheet using this code:

Sub test()
Sheets("Sheet1").Select
Range("B2:B6").SpecialCells(xlVisible).FormulaR1C1 = "=VLOOKUP(rc1,Sheet2!c1:c3,2,FALSE)"
End Sub

This code works well when sheet1 is filtered. But In my case Sheet2 is filtered not sheet1.

I want to do vlookup from sheet1 (Not filtered) to the sheet2 (filtered). Is there any way I can modify my code to achieve this?

Any help is appreciated.
Thank you!

 

[yky]

Finally, it works. Thank you so much for your time.

By the way, what this line do?
IF(Sheet2!$A$2:$A$7=$A2,1)

Appreciated!

You're welcome!

The IF statement says if $A2 is found in Sheet2!$A$2:$A$7, then return 1. This is the first step in solving your problem: is the value in column A of sheet1 also found in the column A of sheet2?

 

[dmadhup]

You're welcome!

The IF statement says if $A2 is found in Sheet2!$A$2:$A$7, then return 1. This is the first step in solving your problem: is the value in column A of sheet1 also found in the column A of sheet2?

I see.
Actually I tried to implement this concept on another data set where I have two different files. And its driving me crazy.....May be I am asking too much. But I don't know why its not working.

If possible, could you please have a look this code:

Range("BB14").FormulaArray = _
"=IF(B14=0,"""",IF(ISNA(INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0)),""No History""," _
& "INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0))"

 

[dmadhup]

I see.
Actually I tried to implement this concept on another data set where I have two different files. And its driving me crazy.....May be I am asking too much. But I don't know why its not working.

If possible, could you please have a look this code:

Range("BB14").FormulaArray = _
"=IF(B14=0,"""",IF(ISNA(INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0)),""No History""," _
& "INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0))"

I am trying to put on cell 14

from this file:

 

[dmadhup]

I am trying to put on cell 14

View attachment 16091


from this file:

View attachment 16092

Basically the formula is this I am using:

INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0)

 

[yky]

Also, I am trying to understand MATCH(1.............
Basically the first argument in Match is lookup value. Right? But I see 1 as a lookup value.

This one is difficult to explain. Yes, 1 is the lookup value. MATCH is looking for 1 in the second argument of MATCH, which is "IF(SUBTOTAL(3,OFFSET(Sheet2!$B$2:$B$7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(Sheet2!$A$2:$A$7=$A3,1))".

You can use the mouse to highlight this part of formula in the formula bar and hit F9. You'll get the result of this part of the formula. It's an array. {FALSE;1;FALSE;FALSE;FALSE;FALSE} is what you'll get. Since MATCH is looking for 1 in this array and 1 is the second element of this array, MATCH will return 2.

 

[dmadhup]

This one is difficult to explain. Yes, 1 is the lookup value. MATCH is looking for 1 in the second argument of MATCH, which is "IF(SUBTOTAL(3,OFFSET(Sheet2!$B$2:$B$7,ROW($A$2:$A$7)-ROW($A$2),0,1))>0,IF(Sheet2!$A$2:$A$7=$A3,1))".

You can use the mouse to highlight this part of formula in the formula bar and hit F9. You'll get the result of this part of the formula. It's an array. {FALSE;1;FALSE;FALSE;FALSE;FALSE} is what you'll get. Since MATCH is looking for 1 in this array and 1 is the second element of this array, MATCH will return 2.

Great!
Actually, I modified the formula to fit in another set of data set.
And my fomula becomes line this:

Range("BB14").FormulaArray =_
INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0)

Master_Quote_NewClosedQuote_History.xlsx]Quote_History1 file look like this:

Do you think I am making any mistake in above formula?

 

[yky]

Please go to MrExcel Message Board/MrExcel Message Board FAQ & Help to see how to use XL2BB to post data. This will save me, and other people who try to help, some, could be a lot of, typing.

 

[yky]

I took a quick glance at your code and found the following:

OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1)

You need to use the same range in OFFSET. Can't have different ranges. Try changing "$B:$B" to "B$14:$B$500" and see what happens. Don't use $B:$B if you don't have to. It'll save some calculation time.

 

[dmadhup]

Please go to MrExcel Message Board/MrExcel Message Board FAQ & Help to see how to use XL2BB to post data. This will save me, and other people who try to help, some, could be a lot of, typing.

Thanks for the suggestion. I will look in to it but my file size little bigger.

So, the above formula I put here in BB cell and looking into Master_Quote_NewClosedQuote_History.xlsx]Quote_History1 file.

The formula I modified like this:

Range("BB14").FormulaArray =_
INDEX('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, MATCH(1, IF(SUBTOTAL(3,OFFSET('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B, ROW($B$14:$B$500)-ROW($B$14),0,1))>0,IF('[Master_Quote_NewClosedQuote_History.xlsx]Quote_History1'!$B:$B=$B14,1)),0),0)

 

[yky]

I see that you have $B:$B in other places of the formula. Change them all to $B14:$B500 and see what happens.