Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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,A2:D2,0),FALSE) -- Regards, OssieMac |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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(A2:D2,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 (A2:D4 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding a Name on a list and returning all matching rows | Excel Worksheet Functions | |||
Finding a cell and returning value from a different row and column | Excel Discussion (Misc queries) | |||
Finding an item in a list & returning a specific value | Excel Worksheet Functions | |||
Lookup info in one Column and then returning info in other columns | Excel Worksheet Functions | |||
Finding, and returning data. | New Users to Excel |