Match multiple values

[willemeulen]

I need to match 3 values as follows:

B5 match with sheet1!A3:A194
C5 match with sheet1!B3:B194
D5 match with sheet1!C3:C194

Return value from column sheet1!H3:H194


W

 

[Ashish Mathur]

Hi,

Assume that your data in H3:H194 is numeric, you may use the following:

=sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5)*(H3:H194))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Krish]

If the data is not numeric, what would be the forumula resulting in
False/True.
Example:
Invoice# Line # Operator#
85687 1 1266
85725 1 1374
85725 2 1374
85725 3 1374
85725 4 1266
85757 1 2455
85757 2 2455
85757 3 1374

Thanks

 

[Ashish Mathur]

Hi,

=and(A3=B5,B3=C5,C3=D5) and then copy down.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[willemeulen]

Unfortunately the following formula doesn't work:

Quote "arthur"

Assume that your data in H3:H194 is numeric, you may use the
following:

=sumproduct((A3:A194=B5)*(B3:B194=C5)*(C3:C194=D5) *(H3:H194))

I edited the formula to this as the lookup table is on another sheet,
sheet1!

=sumproduct((sheet1!A3:A194=B5)*(sheet1!B3:B194=C5)*(Sheet1!C3:C194=D5)
*(sheet1!H3:H194))

It returns #value!, most probably because values are not all numeric

Have a look at the attached file,

Sheet 1 shows the extracted data needed for the lookup (green) and the
data sourced/updated when opened from internet (yellow), I need to
extract the price from column H depending on my input value's on the
compare sheet.

W


+-------------------------------------------------------------------+
|Filename: PIPE COST COMPARISON.xls |
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=164|
+-------------------------------------------------------------------+

 

[Domenic]

I need to match 3 values as follows:

B5 match with sheet1!A3:A194
C5 match with sheet1!B3:B194
D5 match with sheet1!C3:C194

Return value from column sheet1!H3:H194


W

Try...

=INDEX(Sheet1!H3:H194,MATCH(1,IF(Sheet1!A3:A194=B5,IF(Sheet1!B3:B194=C5,I
F(Sheet1!C3:C194=D5,1))),0))

....confirmed with CONTROL+SHIFT+ENTER.

 

[Don Guillett]

It doesn't work because you have VALUE on the source sheet in hidden rows.

 

[willemeulen]

That is how I get the stuff from the net, how can I change that?
Material prices are split in classes on internet and give a header row
each time, luckily hiding the rows does not affect the refreshing of
data, how do I need to solve this, would unhiding rows help?

 

[Don Guillett]

Whoever sent it to you needs to goto an Excel class.
Goto sheet1>select the col b HEADER>f5>special>blanks>OK>entirerow>then
touch the delete key

Sub ClearBlanks()' this macro does the above
Columns("B"). _
SpecialCells(xlCellTypeBlanks). _
EntireRow.Delete
End Sub
then put this.ONE line in cell f5
=SUMPRODUCT((Sheet1!$A$3:$A$154=B5)*(Sheet1!$B$3:$B$154=C5)*(Sheet1!$C$3:$C$154=TEXT(D5,"###"))*Sheet1!$H$3:$H$154)

 

[willemeulen]

Don,

Jerrr:nuts, you're making up some serious formula's/programming for my
skills. Is it possible to modify the web query or am I stuck with how it
comes in. If so it means I have to run a macro or similar (like you
suggest) to keep things automated, my data are always freshly imported
when I open the file. Let try what doctor "Guillett" prescribed and see
where I get stuck.

Thanks,

W

 

[Don Guillett]

OK. I have looked at this some more and it appears that the only data that
comes from the net is in the yellow section (cols d:h). Is it always the
SAME url? Then, it appears that YOU create the formulas that are causing the
problems. I think you need a macro to massage your data to get rid of the
blank rows and then have the macro create your formulas and your sumproduct
formulas will work. If you can't do this then you need to have custom
programming. All of this CAN be done with ONE mouse click. Contact me
privately, if desired.

 

[Don Guillett]

I looked at this again and a major modification to your col a:c formulas
along with major mods to the sumproduct formula negate the need for macros.

 

[hardeep.kanwar]

I need to match 3 values as follows:

B5 match with sheet1!A3:A194
C5 match with sheet1!B3:B194
D5 match with sheet1!C3:C194

Return value from column sheet1!H3:H194


W

Try This

INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE!C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$194&Sheet1!$C$3:$C$194,0))

Confirmed with CTRL+SHIFT+ENTER

 

[Don Guillett]

This solution worked better than mine and did not require a lot of rewrite.
I would only remove the reference to compare! as the formula is on the
compare sheet already. Don't forget to array enter.

INDEX(Sheet1!$H$3:$H$194,MATCH(COMPARE!B5&COMPARE!C5&COMPARE!D5,Sheet1!$A$3:$A$194&Sheet1!$B$3:$B$194&Sheet1!$C$3:$C$194,0))

 

[willemeulen]

This took a while to get figured out, thanks guys for the efforts put in
but the last solution suggested works. For some reason the sumproduct
function did not want to work, even if I removed all formulas and only
focussed on the the first section of the lookup collumn it refused to
return the price, alsmost gave up on it. For me its still a mystery why
it did not want to work while now it works with gaps and everything.

Thanks guys.

W