VLOOKUP evaluating 2 columns

[ddub25]

I've been trying to get a VLOOKUP to look at text in Column B and in Column I (in the same row) on one sheet and then find the exact same text values held in Column BV and Column BW (in another workbook called 'REF' and on a sheet within the workbook called 'ref1'). When it finds these values, I want it to return the text held in Column BX.

I've been struggling to get the correct syntax to incorporate the 2 initial column references. Can anyone help.

Dan

 

[Desu Nota from Columbus]

=SUMPRODUCT(--('[REF.xls]ref1'!$BV$1:$BV$300=B1),(--('[REF.xls]ref1'!$BW$1:$BW$300=I1)),$BX$1-$BX$300)

Try this.

Change your array range (BV1:BV300/BW1:BW300/BX1-300) to match your data.

 

[ddub25]

Thanks. Columns BV, BW and BX are in another workbook. The workbook link is: '[Poten. SU&Mon_MAS_110628.xlsx]Ref'!

Only B811 and I811 are in the workbook where the formula is.

I tried to incorporate this but must have got the syntax wrong for the link as '#VALUE!' is returned.

Here is my formula so far.

=SUMPRODUCT(--(('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130)=B811),(--(('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130)=I811)),$BX$5-$BX$1130)

Dan

 

[Desu Nota from Columbus]

=SUMPRODUCT(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811),(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811),$BX$5-$BX$1130)

Besides the extra parenthesis, there was nothing to cause an error.

Try building the formula yourself using the mouse to select the array ranges:

1) Open both workbooks, and click on the cell where this formula will go.

2) Type: =SUMPRODUCT(

3) Use the mouse to highlight the data from the other workbooks (you'll have to manually type =B811),

4) Do the same for Array 2 ending with =I811,

5) Do the same for Array 3


See if this fixes the problem.

 

[ddub25]

I copied and pasted your formula, but it says that, "the formula contains and error"

I tried constructing the the formula myself, but it also returns an error. Column BX is also on the other sheet so I preceeded the $BX$5:$BX$1130 part of the formula with: '[Poten. SU&Mon_MAS_110628.xlsx]Ref'! also.

Here's the final formula I've tried:

=SUMPRODUCT(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811),(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811),'[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130)

Dan

 

[Desu Nota from Columbus]

hmmm... your formula says, "If value B811 is found in BV5:BV1130 and I811 is found in BW5:BW1130 then return value in BX5:BX1130 from the row where the other two records are found"



You said before that you wanted to search for both values. Is it possible that the values are located on different rows on the other workbook?

That would cause the formula to not return a value, because it is looking for the two adjacent values.


Example:

Search

Value1---Value2------------------------SEARCHBANK1-----Searchbank2---------ReturnValue

Big-------Apple---------------------------Big-----------------Banana--------------1
Small-----Banana-------------------------Small---------------Apple----------------4


If "Big" was in B811 and "Apple" was in I811, then nothing would be returned because although they exist in the search bank, they are not adjacent which is how we are searching them.

 

[Jonmo1]

1. You have an extra ( in there

=SUMPRODUCT(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811),(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811),'[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130)

should be

=SUMPRODUCT(--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811),--('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811),'[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130)


2. Sumproduct is the wrong approach, given the original post clearly states column BX is TEXT.

I want it to return the text held in Column BX.

Sumproduct only works with numbers...


Try this array formula entered with CTRL + SHIFT + ENTER

=INDEX($BX$1:$BX$300,MATCH(B811&I811,[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130&'[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130,0))

 

[Marcelo Branco]

Hi,

As you said that

Column BX is also on the other sheet so I preceeded the $BX$5:$BX$1130 part of the formula with: '[Poten. SU&Mon_MAS_110628.xlsx]Ref'! also.

maybe this Array-formula

=INDEX('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BX$5:$BX$1130,MATCH(1,IF('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BV$5:$BV$1130=B811,IF('[Poten. SU&Mon_MAS_110628.xlsx]Ref'!$BW$5:$BW$1130=I811,1)),0))

confirmed with Ctrl+Shift+Enter

HTH

M.

 

[ddub25]

Thanks the three of you for helping. I used Marcelo's formula in the end which seemed to work fine with CSE. Jonmo, i kept getting a message saying that 'the name was invalid' and it would highlight: '[Poten. in the formula bar. I was using CSE as you suggested with yours as well Jonmo. Was there maybe a parenthesis error in it?

Thanks again

Dan

 

[Marcelo Branco]

Thanks the three of you for helping. I used Marcelo's formula in the end which seemed to work fine with CSE. Jonmo, i kept getting a message saying that 'the name was invalid' and it would highlight: '[Poten. in the formula bar. I was using CSE as you suggested with yours as well Jonmo. Was there maybe a parenthesis error in it?

Thanks again

Dan

Tks for the feedback. Glad to help

M.