Hi,
I have a slightly different problem which was solved here:
http://www.excelforum.com/excel-gene...ml#post2645762
But what I would like help with please is a index/match formula which looks up a product and customer specific price, if one is not found or less than zero then I would like it to return the average.
The formula I would need would be on “1”sheet, Cell Q23 .
I would want it to match two things, the customer number which is on “1” sheet, cell G4 and this:$C23&Q$18&$D23&Q$19
The table I need it to match is on the “overview” sheet, range H5:S174. I would like it to match the customer number (row 3) product (column H)
If the answer is less than zero I would like it to return the average.
Can anyone help in any way?
Paul
Last edited by pauldaddyadams; 11-18-2011 at 09:43 AM.
Try:
and to not display errors:=INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))
=LOOKUP(9.999E+307,CHOOSE({1,2},0,INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))))
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi
Where would the code go to not display errors? Is it in a different cell?
Same cells, ie. Q23, then copyied down and across.
Note that it will return a 0 instead of #N/A error...
If you have Excel 2007, however you can use IFERROR:
and you can easily replace the 0 at the end with "" for blanks..=IFERROR(INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0)),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Hi,
That code works very well but wondered if you could help tweeking it?
Rather than display "0" iferror, can it display the corrosponding average result (column S)
column S on which sheet? If an error occurs, it means that a matching row wasn't found in Overview sheet.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I know why it returns an error thinking about it so if possible can it be an IF(OR) formula.
So if it is an error or the result is less than 1, it returns the average on the sheet "overview" This will always be the last column on the overview sheet.
So, if there is no match or result is less than 1, you want the average of all of column S?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
if there is no match or less than 1 I would like it to return the average of the column S but only for that particlar product.
So it would still need to find the row that matches this: $C23&Q$18&$D23&Q$19
e.g. Q23 and Q24 on the attached are both less than one so I would need it to lookup the overview tab for the products
(Q23) EnergiKareAluminiumToughenedNoNo = Row 9, column S (17.30)
(Q24) EnergiKareSuperToughenedNoNo = Row 10, column S (14.56)
File attached
LaminatedToughenedAluminumNoNo for Q35 doesn't exist in Overview.. then what?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
None of the laminated will appear so if it does return an error can it return zero?
If possible:
IfError, result should be zero
If <1, result should use the average
try, in Q23,
copied down and across=IFERROR(IF(INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))<1,INDEX(Overview!$S$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0)),INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Wowzers - I am always amazed with what excel can do but more importantly the helpfulness of members of this forum!
Thank you NBVC!!!
One other quick thing.
My average column will change with more data, is this the part I change?
INDEX(Overview!$S$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0))
Should I change column S here?
Yes it would be the S references you'd change.
Here is another, more versatile formula for that. It now looks for column entitled "Average" within the whole range to find which column to call... so if you insert columns between H and S on Overview, then it should follow properly along.
=IFERROR(IF(INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))<1,INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH("Average",Overview!$H$4:$S$4,0)),INDEX(Overview!$H$5:$S$174,MATCH($C23&Q$18&$D23&Q$19,Overview!$H$5:$H$174,0),MATCH($G$4,Overview!$H$3:$S$3,0))),0)
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks