Array Formula in place of vlookup?

**Barking_Mad** · 03-15-2022, 06:17 AM

Hi,

I have the following formula to look up a cell value (A3) using VLOOKUP and if it matches the lookup value, put "0", otherwise leave it blank.

Please Login or Register  to view this content.

The issue of course is that if the first value in the lookup doesn't match A3 then it simply stops rather than finding the match. I think this is done by Index Match or an Array, but I can't remember what the formula should be and my google-fu skills are failing.

Can anyone help?

Edited: Attached sheet
Edited #2: Amended formula example above to match attachment.
Edited #3: Anebded example to show expected results.

**Glenn Kennedy** · 03-15-2022, 06:19 AM

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

**AliGW** · 03-15-2022, 06:20 AM

Why not this?

=IF(I$1=IFNA(VLOOKUP(A3,'Report'!$A$2:$M$10001,13,FALSE),0),0,"")

Don't put 0 into inverted commas unless you want text returned instead of a real number.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) 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. Thanks.

**Barking_Mad** · 03-15-2022, 06:44 AM

APologies, sheet uploaded. I was trying to do it via the paperclip, but that wasnt working, only remembered about 'manage attachments' when I saw it further down

Profile update too

**Glenn Kennedy** · 03-15-2022, 06:50 AM

I'm still somewhat baffled... is this what you want?

=IF(B$1=VLOOKUP($A2,$G$2:$H$6,2),0,"")

copied across and down. if not, please amend the sheet to show your EXPECTED results...

**nick.williams** · 03-15-2022, 07:08 AM

Your test sheet is not quite the same as your example formula. You seem to have used absolute references for things which is not quite correct. It also looks like you've made a mistake in a couple of cells (e.g. C2 and D2 both refer to $C$1).

Taking your formula in B2 as a starting point:

=IF($B$1=VLOOKUP(A2,$G$2:$H$6,2),0,"")

The $B$1 reference (in red) should be B$1 which will keep it in the first row but move along the columns when copied. You had this correct in the formula in your first post.

The A2 reference (blue) should be $A2 which will keep it in column A but move down the rows when copied.

At the end of the VLOOKUP statement you have omitted ,FALSE which you correctly had in your first post. (I use 0 instead of FALSE as it's quicker to type)

So you should end up with:

=IF(B$1=VLOOKUP($A2,$G$2:$H$6,2,0),0,"")

This works but will give you #N/A for ID 4 which is not in your reference table. To avoid this you could wrap it in IFERROR, e.g.

=IFERROR(IF(B$1=VLOOKUP($A2,$G$2:$H$6,2,0),0,""),"")

NB You can use INDEX and MATCH instead of VLOOKUP if you prefer, it's a similar solution:

=IFERROR(IF(B$1=INDEX($H$2:$H$6,MATCH($A2,$G$2:$G$6,0)),0,""),"")

**AliGW** · 03-15-2022, 07:09 AM

@BarkingMad

Any response to my suggestion? Maybe you missed it.

**Barking_Mad** · 03-15-2022, 07:10 AM

Originally Posted by Glenn Kennedy

I'm still somewhat baffled... .

Hi,

I've updated the sheet. Hopefully Ive replaced my crap example

Column C (C2:C5) should populate with 0 for all the IDs as per column F, but it doesn't, presumably as it stops at the first vlookup (?)

**Barking_Mad** · 03-15-2022, 07:11 AM

Originally Posted by AliGW

Why not this?...

Hi,

Sorry, trying to do 10 things at once...This didnt seem to work, but im possibly misunderstanding something

**AliGW** · 03-15-2022, 07:14 AM

Thanks for updating your profile.

**nick.williams** · 03-15-2022, 07:17 AM

Ah I see what you mean now, how about this (in B2, copied to others):

=IF(SUM(($E$2:$E$11=$A2)*($F$2:$F$11=B$1))>0,0,"")

**Glenn Kennedy** · 03-15-2022, 07:19 AM

That is NOT what I asked for. I have no idea what you are trying to achieve... I need to see EXPECTED answers... and not a formula that does not work

**Barking_Mad** · 03-15-2022, 07:54 AM

Originally Posted by Glenn Kennedy

That is NOT what I asked for. I have no idea what you are trying to achieve... I need to see EXPECTED answers... and not a formula that does not work

Hi,

Ive put 0 in red in the other column to show you what's EXPECTED. Thanks.

**AliGW** · 03-15-2022, 07:59 AM

This seems a very odd thing to be doing - it would help to explain this in context.

**Glenn Kennedy** · 03-15-2022, 08:07 AM

I have absolutely no idea what you are trying to achieve... one more random guess....

=IF($B$1=VLOOKUP($A2,$E$2:$F$11,2),0,"")

copied across and down.

**nick.williams** · 03-15-2022, 08:11 AM

I still think my solution works...

As far as I can tell, the issue the OP had was that they are running a test for if something equals a VLOOKUP result, but want to check against all possible results rather than just the first.

**Barking_Mad** · 03-15-2022, 08:12 AM

Originally Posted by AliGW

This seems a very odd thing to be doing - it would help to explain this in context.

Hi Ali,

It's basically an absence tracking sheet.

Columns F:G are on one sheet which is added to each week (wk1, then wk2 etc.) with students who are absent.
Columns A:D are on another sheet with all the students on. They reflect any absences by putting a 0 (which conditionally formats to red) when a student appears on the other sheet.

It's not an ideal way of doing things but unforutnately those 'in charge' devised an expensive absence system with no front end for users) #highereducation

**AliGW** · 03-15-2022, 08:14 AM

There's nothing in columns D or G ...

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C	E	F
1	ID	Wk1	Wk2	ID	Week
2	1	0	0	1	Wk1
3	2	0	0	2	Wk1
4	3	0	0	3	Wk1
5	4	0	0	4	Wk1
6	5	0	0	5	Wk1
7				1	Wk2
8				2	Wk2
9				3	Wk2
10				4	Wk2
11				5	Wk2

Sheet: Sheet1

**Barking_Mad** · 03-15-2022, 08:15 AM

Originally Posted by nick.williams

I still think my solution works...

As far as I can tell, the issue the OP had was that they are running a test for if something equals a VLOOKUP result, but want to check against all possible results rather than just the first.

Hi, just trying to test it now.... And yes, if the user ID appears in the list in columns F:G, then it should be reflected in the appropriate Wk column in B:D. I think the issue is that it only checks the first value found for the ID in column F, rather than the ones that provides the match.

**Barking_Mad** · 03-15-2022, 08:16 AM

Originally Posted by AliGW

There's nothing in columns D or G ...

AliGW on MS365 Insider (Windows) 64 bit

	A	B	C	E	F
1	ID	Wk1	Wk2	ID	Week
2	1	0	0	1	Wk1
3	2	0	0	2	Wk1
4	3	0	0	3	Wk1
5	4	0	0	4	Wk1
6	5	0	0	5	Wk1
7				1	Wk2
8				2	Wk2
9				3	Wk2
10				4	Wk2
11				5	Wk2

Sheet: Sheet1

Sorry, I meant E:F

**AliGW** · 03-15-2022, 08:16 AM

Still no idea why you want to return 0. To me, this is what you appear to be describing:

=COUNTIFS($E$2:$E$11,$A2,$F$2:$F$11,B$1)

**rorya** · 03-15-2022, 08:19 AM

If they only appear once per week for absences, I'd have thought just:

=1-COUNTIFS($E:$E,$A2,$F:$F,B$1)

in B2 copied across and down. If they could appear more than once per week, you can use:

=1-(COUNTIFS($E:$E,$A2,$F:$F,C$1)>0)

**Barking_Mad** · 03-15-2022, 08:24 AM

Originally Posted by AliGW

Still no idea why you want to return 0. To me, this is what you appear to be describing:

=COUNTIFS($E$2:$E$11,$A2,$F$2:$F$11,B$1)

It was basically checking to see if the value existed (0 was an arbitary number to show non-attendance), but I didnt think of using COUNTIF instead of vlookup. Thanks for your patience, this works just as well.

**Barking_Mad** · 03-15-2022, 08:26 AM

Thanks for your help everyone. Ill ensure to be more thorough with my original post next time

**Glenn Kennedy** · 03-15-2022, 08:33 AM

Woo Hoo! Someone figured it out!!!

**AliGW** · 03-15-2022, 08:37 AM

By accident, it seems ... Thanks for the rep.

Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Re: Array Formula in place of vlookup?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Non Array Formula to Extract Unique Values in column A2:A61 and Place in B2:B61

[SOLVED] Using VBA in place of array formula

[SOLVED] Array SUMIF formula to sum if numbers but it not place text

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions