Exclude certain rows from the table array in the VLOOKUP

**Mhabashy** · 10-18-2022, 04:19 AM

Hello,

I need to exclude certain rows from the table array in the VLOOKUP formula exactly the two rows under the LOOKUP value, this formula is a part of big formula so i cannot use macro to solve it.
I need to tell as following:

VLOOKUP(M9,M:N-(M9:N11),2)

any other tip.

**Pete_UK** · 10-18-2022, 04:31 AM

Instead of using full-column references for the table, you could use this:

=VLOOKUP(M9,M$12:N$5000,2)

(you can change the 5000 if you have more data).

Hope this helps.

Pete

**Mhabashy** · 10-18-2022, 04:37 AM

thanks Pete_UK for your reply but the problem i need the formula to search above and under M9 except the M9,M10,M11
and its variable when the LOOKUP value is M40 so i need the search to be above and under except M40,M41,M42.

**AliGW** · 10-18-2022, 04:56 AM

There are instructions at the top of the page explaining how to attach your sample workbook. Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Is you forum profile showing the version of Excel that you need this to work for?

Members will tailor the solutions they offer to the version of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this.

The three most recent versions of Excel are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the release number in your profile (e.g. MS365 Version 2211). This is in the About Excel section further down the Account page.

Thanks.

**Mhabashy** · 10-18-2022, 05:41 AM

Thanks AliGW for your kind reply
I already edit my post with sample attachment and updated my profile.

thanks.

**AliGW** · 10-18-2022, 05:45 AM

Explain the logic behind the answers you are expecting. What's the thought process?

**menem** · 10-18-2022, 06:39 AM

I've try this formula

=IF(COUNTIF(M$7:M7,M7)=1,LOOKUP(2,(1/($M$7:$M$27=M7)),$N$7:$N$27),VLOOKUP(M7,$M$7:$N$27,2,FALSE))

but not sure about the result

Regards.

**Mhabashy** · 10-18-2022, 07:14 AM

Thanks menem

very much
its working, i will trying to add it to my big formula the problem its very complicated now

the problem is its mentioned specific range till row 27 put the actual table is very long and variable

i will wait for this night maybe i can get shorter and more flexible formula or i will highlight it as Solved.

thanks again,

**Pete_UK** · 10-18-2022, 02:01 PM

From Menem's picture, it looks like you want the maximum of column N for a given letter in column A. You might be able to use a MAXIFS function (not sure if it is available in XL2016), or an array formula based on MAX(IF...

Hope this helps.

Pete

**Mhabashy** · 10-19-2022, 01:40 AM

Thanks Pete i will try your suggestion, i tried to find any way to exclude some rows from the VLOOKUP table array formula but i think no way like that

thanks for your help

**Pete_UK** · 10-19-2022, 04:59 AM

If you do have the MAXIFS function available in your version of Excel, then you can use this formula in cell O7:

=IF(MOD(ROW(),3)=1,MAXIFS($N$7:$N$27,$M$7:$M$27,M7),"")

and if so, please update your profile to show the version you are currently using. If not, then you can use this array* formula instead:

=IF(MOD(ROW(),3)=1,MAX(IF($M$7:$M$27=M7,$N$7:$N$27)),"")

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE) instead of the usual Enter.

Copy the formula down as required (into every cell, no need to just paste it into every 3 cells).

I'm not sure why your isolated cells for A and C should have the values that you state - please explain the logic for these.

Hope this helps.

Pete

**Mhabashy** · 10-20-2022, 02:17 AM

Thank you MR, Pete
i am really appreciating your efforts.
the excel i mentioned is only as example because the original is big with complicated formulas and the one i am asking about is a part of long formula, and actually i am always need this type of formula to exclude range from the searching area i will try to understand your formula maybe i can use it in the future.

i am grateful to your help, thanks alot,

Maney

**Pete_UK** · 10-20-2022, 05:44 AM

You're welcome - thanks for the rep.

Pete

Exclude certain rows from the table array in the VLOOKUP

LinkBack

Thread Tools

Rate This Thread

Display

Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Re: Exclude certain rows from the table array in the VLOOKUP

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Exclude Blank Rows With Array

[SOLVED] table(A) as an array using all rows but only selected columns to compare with table(B)

Add EXCLUDE Option in table to Exclude from Solver

Create an array based off values in another array - exclude blanks

[SOLVED] vlookup to exclude rows when a cell is 0?

VLOOKUP Table array - Refrence cell instead of munally re-enter array

VLOOKUP - 3 Table Array

Bookmarks

Bookmarks

Posting Permissions