Vlookup, Index & Match - Grab values and compare

[cybid]

Greetings to all.
First of all, I am new to this forum and need the help of your excel knowledge.

I have a workbook with the Sheets: A, B, C. On the B sheet I am collecting data from sheet A, and trying to collect other data from sheet C. Since the VLOOKUP wont collect negative col_index_num, I am trying to find a solution with index and match formula, but can't manage it. I am posting 2 images to give a visual of my problem. If you need other info or more details, please let me know and maybe I can upload the document for you. As you can see on the image number 2 I need to collect data with blue circle- only the value next to the 'direct:' text.

Thnx in advance.

 

[jasonb75]

I wasn't following before because what you said didn't match the earlier example, but I can see what you mean now from the capture.

You could try using a 2 part formula like this, which will first check for Direct 4 rows above the code, if it is not found then it will check 2 rows above.

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$13:$A$302)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$13:$L$302="Direct:"),1)),""))

As before this should work with the example provided, but given the layout of the source data, errors are to be expected.

If it doesn't work as needed this time, then I would suggest that you look to improve the data source.

 

[cybid]

I wasn't following before because what you said didn't match the earlier example, but I can see what you mean now from the capture.

You could try using a 2 part formula like this, which will first check for Direct 4 rows above the code, if it is not found then it will check 2 rows above.

=IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$11:$A$300)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$11:$L$300="Direct:"),1)),IFERROR(INDEX(WasteTOSHIBA!$P:$P,AGGREGATE(15,6,ROW(WasteTOSHIBA!$P$13:$A$302)/(A3=WasteTOSHIBA!$A$15:$A$304)/(WasteTOSHIBA!$L$13:$L$302="Direct:"),1)),""))

As before this should work with the example provided, but given the layout of the source data, errors are to be expected.

If it doesn't work as needed this time, then I would suggest that you look to improve the data source.

This one works as needed and is giving great results. I wanna thank you for your time helping me and it's been a great lesson.