Matching Formula has me Stumped!

[Restyl]

I've been working on this one for a bit with no success. I've outlined what I'm trying to accomplish below:

Currently, the return works very well, but only with exact matches, everything else throws me an ERROR. I tried modifying the match_type which causes me to throw an error for some reason. What am I doing wrong?

Here is my current formula:

=IF(ISBLANK('Quoting Form'!$F$8), "???", IFERROR(INDEX('Motor Data'!$S$10:$S$15,MATCH('Quoting Form'!$D$8,INDEX('Motor Data'!$T$10:$X$15,,MATCH('Quoting Form'!$D$4,'Motor Data'!$T$9:$X$9,0)),0)),"ERROR"))

 

[Aryatect]

Hi Changing the outer Match to Greater than instead of exact match should work:

=IF(ISBLANK('Quoting Form'!$F$8), "???", IFERROR(INDEX('Motor Data'!$S$10:$S$15,MATCH('Quoting Form'!$D$8,INDEX('Motor Data'!$T$10:$X$15,,MATCH('Quoting Form'!$D$4,'Motor Data'!$T$9:$X$9,0)),-1)),"ERROR"))

I tried replicating the same it worked for me:

	A	B	C	D	E	F	G	H	I	J	K
1	Contractor	120	208	230	240	480			Voltage		HP
2	A	135	40	50	100	100			480		11
3	B	90	25	30	30	50
4	C	45	10	15	15	25
5	D	27	7.5	7.5	7.5	10			Formula
6	E	18	3	3	3	5			C
7	F	9	1.5	1.5	1.5	2

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula I6 =INDEX($A$1:$A$7,MATCH(K2,INDEX($A$1:$F$7,,MATCH($I$2,A1:F1,0)),-1)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Hope this helps!

I've been working on this one for a bit with no success. I've outlined what I'm trying to accomplish below:

Currently, the return works very well, but only with exact matches, everything else throws me an ERROR. I tried modifying the match_type which causes me to throw an error for some reason. What am I doing wrong?

Here is my current formula:

=IF(ISBLANK('Quoting Form'!$F$8), "???", IFERROR(INDEX('Motor Data'!$S$10:$S$15,MATCH('Quoting Form'!$D$8,INDEX('Motor Data'!$T$10:$X$15,,MATCH('Quoting Form'!$D$4,'Motor Data'!$T$9:$X$9,0)),0)),"ERROR"))

 

[Restyl]

Hi Changing the outer Match to Greater than instead of exact match should work:

=IF(ISBLANK('Quoting Form'!$F$8), "???", IFERROR(INDEX('Motor Data'!$S$10:$S$15,MATCH('Quoting Form'!$D$8,INDEX('Motor Data'!$T$10:$X$15,,MATCH('Quoting Form'!$D$4,'Motor Data'!$T$9:$X$9,0)),-1)),"ERROR"))

I tried replicating the same it worked for me:

	A	B	C	D	E	F	G	H	I	J	K
1	Contractor	120	208	230	240	480			Voltage		HP
2	A	135	40	50	100	100			480		11
3	B	90	25	30	30	50
4	C	45	10	15	15	25
5	D	27	7.5	7.5	7.5	10			Formula
6	E	18	3	3	3	5			C
7	F	9	1.5	1.5	1.5	2

<tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula I6 =INDEX($A$1:$A$7,MATCH(K2,INDEX($A$1:$F$7,,MATCH($I$2,A1:F1,0)),-1)) <tbody> </tbody>

<tbody>
</tbody>

Hope this helps!

Thanks so much for looking at this. I had tried changing the outer match to greater than and had no luck... I just found the solution after a painful amount of debugging

I was using Data Validation Lists for my search Value, HP. This was throwing an error because the number was formatted as "Text" in the Data Validation list, and formatted as a "Number" in the lookup list. Thus, error.

Sometimes its the "obvious" stuff

 

[Aryatect]

so true