Combine IFERROR and VLOOKUP (am in barking up the wrong tree here?)

**frankgbr** · 11-05-2012, 09:25 AM

Can someone help me with a formula please, I'm trying to get excel to search a column and return a value based on a value in a drop down box and a value in anther cell.

For example.

Cell A15 (on sheet 1) has a drop down box with 18 different options to select from (eg option 1 is ARAMID YARN in cell B4, carbon Yarn C4 etc), this is located on sheet 2. Cell B17 on sheet 1 has a value. In this case the value is 78.4.

Question 1. What is the formula i need to search the values in Sheet 2 (b5 to b30) in order to find the equal to or greater than value based on on the data in the drop box (sheet 1 A15) and the vaule in Cell B16 on Sheet 1?

Question 2. Can this be automated so when i select a new option in the drop down box it filters through the corresponding cells and selects the correct value based on the value of the new drop box ption and the value of sheet 1 B17 etc...

Does this make sense to anyone?

**Fotis1991** · 11-05-2012, 09:40 AM

Maybe will make sense if you upload a small sample workbook

**frankgbr** · 11-05-2012, 09:49 AM

Here you go.

Thanks.

**crzu** · 11-05-2012, 11:46 PM

@ frankgbr

Just to confirm. As your file show correct dpi is 14,700.

Not sure if I understand correctly, is the search result to be 12,600 (Sheet 2, A9) instead?

The search condition would be 78.4 (Sheet 1, B17) and 71.5 (Sheet 1, E17)

In sheet 2
Marathon 600 (100% Aramid) -- 71 (B9)
Marathon 680 (40% IRC Carb / 60% Aramid) -- 87 (C9)

**:) Sixthsense :)** · 11-06-2012, 12:06 AM

Originally Posted by frankgbr

Here you go.

Thanks.

The below formula will get 12600 as result, I could not find any possibilities to get 14,700 since the logic is missing

Formula:

Please Login or Register  to view this content.

**FDibbins** · 11-06-2012, 12:13 AM

you're description of what you want, and the file you uploaded, do not match. there is no drop=down in A15. B16 is empty, but see if the attached gives you what you want?

i have included a range name called headings on sheet2, and used this for the drop-down on sheet1

edit: apologies, use this formula instead of what i gave you...

=INDEX(Sheet2!$A$4:$S$30,MATCH(B17,Sheet2!$B$4:$B$30,1)+IF(ISERROR(MATCH(B17,Sheet2!$B$4:$B$30,0)),1,0),1)

(@ Six check the 1st match to see how I arrived at = or greater than)

**frankgbr** · 11-06-2012, 07:59 AM

Problem Solved
C/o Peter!
Thanks for all the help though!

=OFFSET(Sheet2!$A$4,
MATCH(B17,
OFFSET(Sheet2!$A$4,0,
MATCH(A15,Sheet2!$4:$4,0)-1,
MATCH(1E+99,Sheet2!$A:$A,1),1),1),0)
Explanation
• MATCH(1E+99,Sheet2!$A:$A,1) finds the row of the bottom of your data on Sheet2
• MATCH(A15,Sheet2!$4:$4,0) finds the column number for the contents of the dropdown in cell A15
• The inner OFFSET makes an appropriate column range for the final MATCH to search in
• MATCH(B17,... matches the minimum required AM in the appropriate column
• The outer OFFSET returns the desired value from column A
Peter

**Fotis1991** · 11-06-2012, 08:05 AM

Who is Peter?

**frankgbr** · 11-06-2012, 08:09 AM

A genuis...

http://excelusergroup.org/members/PeterG/default.aspx

Thanks again for your help.

**Fotis1991** · 11-06-2012, 08:23 AM

My Congr to him!

Really nice formula!

**Fotis1991** · 11-06-2012, 08:31 AM

And please take a look to this for understanding why it's not good to cross post(i spend some hours today morning to find a solution for you, while Peter offered you a solution since Yesterday!) .

http://www.excelguru.ca/content.php?...-cross-posters