Phone/fax based on list

**kmr296** · 03-04-2010, 11:45 AM

I'm trying to input phone and fax numbers into cells based on the name of a contractor selected from a drop down box. I though I had this figured out no problem but i'm getting #Num! Error and its driving me nuts. On one sheet I have a contractor list column A, phone column B, fax column C. I'm using the formula =SMALL(IF(Contractors!A1:A5000='Bid Sheet Page 1'!$C$9,ROW(Contractors!A1:A5000),9999),ROW()-1) to show the row number on the "contractors list" sheet that corresponds with the given. For example, lets say a user selects ATZ from the drop down box. Row A on my sheet will show the row number of ATZ on the contractors list. Using that row number I will then use =IF($A2<>9999,INDEX('Contractor list'!$A$1:$H$10144,'Bid Sheet 1'!$A2,COLUMN('Bid Sheet 1'!B2)),""). I haven't got to the second part yet but I think I should be ok there. Whats the problem with my first formula? See my attached sheet.

**JeanRage** · 03-04-2010, 11:53 AM

Hi,

You can try

=INDEX(Contractors!B:B,MATCH(C9,Contractors!A:A,0))

HTH

**6StringJazzer** · 03-04-2010, 12:05 PM

I agree with that answer, and thought it might be instructive to explain why your formula doesn't work.

It looks like you are expecting the IF to match the value you are looking for within a range, and then return the corresponding row number. IF is not that sophisticated. It looks at a logical expression and then returns a value.

**kmr296** · 03-04-2010, 12:19 PM

I have this formula working great on this sheet - i'm trying to duplicate these results, but on a different document. Check this one out. Can you show me why this one works but the new one wont?

**JeanRage** · 03-04-2010, 01:35 PM

Hi,

The first step is to use the match() function in order to determine the row number ...
Then based on the right column, the function index() will return the value you are looking for ...

HTH

**kmr296** · 03-04-2010, 03:00 PM

Ok - So i got the formula down, and it works for 2 of the twelve rows - why not the whole thing? Any ideas?

**6StringJazzer** · 03-04-2010, 06:13 PM

I have no idea why it worked on any at all. The third argument for INDEX has to be a column number, not an entire column. Since your array (the first argument) contains only one column, your column number is 1.

Use this formula in I9 then copy to the rest:

=IF(A9="","",INDEX('Contractor List'!B:B,A9,1))