INDEX MATCH or VLOOKUP formula based on a condition

**apple_tree** · 09-14-2016, 04:07 PM

Dear all,
I have tried to google internet and search this board without any success.
I have two tabs: Tab A and Tab B.
I need to look up a Code for the Tab A using "Item #" which is also available in TAB B. However, the problem is that in the Tab B there are usually few rows with the same "Item #" and sometimes they do not have "Code" numbers. That is why VLOOKUP does not work.
I need to find the Code number for the "Item#" with a condition that "Resource" in Tab B has a value " Labor" or "Admin Labor". Because usually the rows which have Resource as "Labor " or "Admin Labor" have Code specified.

thank you

**AlKey** · 09-14-2016, 04:14 PM

Enter this array formula in C3 and copy down

Formula:

=IFERROR(INDEX('Tab B'!D:D,SMALL(IF(('Tab B'!B$3:B$10=B3)*('Tab B'!$D$3:$D$10<>""),ROW('Tab B'!B$3:D$10)),ROWS(C$3:C3))),"")

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Or try this non array version

Formula:

=IFERROR(INDEX('Tab B'!D$3:D$10,INDEX(MATCH(1,('Tab B'!B$3:B$10=B3)*('Tab B'!D$3:D$10<>""),0),0)),"")

**apple_tree** · 09-14-2016, 05:34 PM

I have not tried second formula. I will try it in a few minutes.

**apple_tree** · 09-14-2016, 05:33 PM

AlKey,
thank you so much for a quick reply. Do you mind quickly explaining what SMALL and ROWS mean because i am trying to copy this formula to my larger set of data and it works for some rows but not for all.
Also in the end is it supposed to be ROWS(C$3:C3) or ROWS(C$3:C$3)?
Also i really would like somehow to insert the Condition that Column Resource in Tab B is either "Labor" or "Admin Labor" because there are many other rows with the same Item # but different Resources and they have wrong Codes. I only need Codes which are for Labor and Admin Labor Resources.

**AlKey** · 09-14-2016, 05:41 PM

The ROWS part should be exactly as written ROWS(C$3:C3)
For other details of an array formula please follow the link below.

http://www.exceltactics.com/make-fil...using-small/4/

**apple_tree** · 09-15-2016, 01:03 AM

I have made some progress but I was not able to completely resolve the problem...
I have read about the "INDEX SMALL IF ROW ROWS" function but for some reason it did not work for my table.

I have tried INDEX MATCH =IFERROR(INDEX('Tab B'!D$3:D$10,INDEX(MATCH(1,('Tab B'!B$3:B$10=B3)*('Tab B'!D$3:D$10<>""),0),0)),"")
and it worked better.
However, INDEX MATCH made few mistakes. Like I mentioned before there are some rows when I have
multiple Code numbers. And I only need to pick ones which are corresponding to the Resource "Labor" or "Admin Labor"
in the original table.
I am attaching the updated file so you can see. When use the formula it picks up Code R560 for the first row but has to pick up one which
is for the "Labor" Resource, A001.
hope it makes sense.

**Glenn Kennedy** · 09-15-2016, 03:30 AM

This will work in C3, copied down:

=IFERROR(INDEX('Tab B'!D$3:D$10,(MATCH(1,('Tab B'!$A$3:$A$10='Tab A'!A3)*('Tab B'!$B$3:$B$10='Tab A'!B3)*(IF(ISERROR(SEARCH("Labor",'Tab B'!$C$3:$C$10)),FALSE,TRUE)),0))),"")

**Glenn Kennedy** · 09-15-2016, 03:32 AM

Oops. It's an array formula, BtW.

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

**AlKey** · 09-15-2016, 07:26 AM

Try this

Formula:

=IFERROR(INDEX('Tab B'!D:D,SMALL(IF((ISNUMBER(SEARCH("labor",'Tab B'!C$3:C$10)))*('Tab B'!B$3:B$10=B3),ROW('Tab B'!B$3:C$10)),1)),"")

***Array formula
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.
You will know the array is active when you see curly braces { } appear around your formula.
If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

And with regular

Formula:

=IFERROR(INDEX('Tab B'!D$3:D$10,INDEX(MATCH(1,('Tab B'!$B$3:$B$10=B3)*INDEX(ISNUMBER(SEARCH("Labor",'Tab B'!$C$3:$C$10)),),),)),"")

**apple_tree** · 09-15-2016, 06:16 PM

ALKey, thank you for your assistance.
I tried the second INDEX MATCH formula with SEARCH. It works only for the rows which have "Labor" but not for "Admin Labor". If i could only add OR somehow. Labor or Admin Labor.

**AlKey** · 09-15-2016, 08:27 PM

Actually, both formulas pick up "Labor" and "Admin Labor". This is why I used SEARCH function so it will look for any text string that contain word "labor" and this is evident from the post #9 (see attached file). As a test, replace word "labor" in the formula with "Admin Labor" and you will see that both formulas will not pick up the first code they will pick up the second code only.

Regards,

**Glenn Kennedy** · 09-16-2016, 02:31 AM

Apple_tree. Did you actually open the sheets posted by Alkey (#9) and myself (#7) Did the formulae do what you wanted?

INDEX MATCH or VLOOKUP formula based on a condition

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Re: INDEX MATCH or VLOOKUP formula based on a condition

Thread Information

Users Browsing this Thread

Similar Threads

If formula to match 2 conditions and vlookup to match a 3rd condition

VLOOKUP/INDEX MATCH formula for finding cost of toner based on printer name & model

Index and Match with based on IF then condition?

[SOLVED] Vlookup (IFS) 3 Condition Model Using Index /Match

VLOOKUP or INDEX MATCH where the reference MATCH cell has a formula behind it

[SOLVED] Conditional formatting based on VLOOKUP/INDEX:MATCH

INDEX/MATCH & VLOOKUP based on month number

Bookmarks

Bookmarks

Posting Permissions