If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Finding a value and returning corresponding info
Hi Vicky,
The following formula assumes. Your columns start at column A (or column 1) because the column numbers are returned by the Match function. Your low number is in column E because that number is used in the Match function. Your provider column is column F. The formula below is in cell F2. (Can copy down) =INDIRECT("R1" & "C"&MATCH(E2,A22,0),FALSE) -- Regards, OssieMac |
#2
|
|||
|
|||
Finding a value and returning corresponding info
Hi again Vicky,
I have not handled the following. "The one issue is what if there are two providers with the same number." I don't know how to list multiple providers but you could use another column and count the minimum values in the range like the following. =COUNTIF(A22,E2) Another suggestion is to use conditional formatting and highlight the multiple minimum values in each row with a background (interior) color. To do this:- Select the range of values (A24 in your example). Select Conditional format and formula (Depends on your version of xl for exact instructions for this.) Enter the following formula. =AND(A2=$E2,COUNTIF($A2:$D2,$E2)1) Set the required format Note when setting conditional format you set the formula as if it applies to only the first cell of the selection and xl looks after applying correct formula to the rest. However, you do need to think about the absolute addressing. In this case it is required on the columns but not on the rows. -- Regards, OssieMac |
Thread Tools | |
Display Modes | |
|
|