Rahul Mishra
Member
Dear Sir,
I attached one excel sheet for data
i want data in yellow column
I attached one excel sheet for data
i want data in yellow column
Required data
- Thread starter Rahul Mishra
- Start date
Rahul Mishra
Member
thanks for it but i required this without vba only in excel
Rahul Mishra
Member
sir this is ok but i don't able to understand that why use small function in it and can i put single formula for row and column
PCosta87
Well-Known Member
I'm attaching single formula for both row and column... merged using a simple IF 
As for the SMALL function... first you need to understand the concept behind the INDEX function.
INDEX can return a value in a specific position of an array, be it the 1st, 2nd, 3rd and so on.
Usually you would go for an INDEX/MATCH combination, but match does not seem to work across multiple columns/rows. To overcome this limitation, we use a combination of SMALL/IF entered as an array.
As you may know, SMALL allows you to select which value to return: the smallest, the 2nd smallest and so on.
Combining it with the IF function in array form, you basically get an array of results matching the criteria (in this case "=1200"). What we are looking for here is the ROW to use in the INDEX, so, IF TRUE, it will return the ROWS where it matches 1200. Using ROW(I1) gives you "1" (first match) and by not being an absolute reference when copied down will return 2 (ROW(I2)), 3 (ROW(I3)) and so on.
The same can be applied to the "Make", only now it will have to match both "=1200" and the service provider already found, hence the "IF(($A$2:$E$10=$H$3)*($A$2:$A$10=$I3)"... * allows for more conditions, similar to AND.
In the end the function comes down to:
"INDEX(array: Service providers or Makes, position: ROW where it finds the match) The position will be the row of the nth value of the array found with the SMALL function.
It is quite hard to explain so I hope I didn't mess up
You can also look it up online or use the evaluation tool in Excel to go through the steps.
In any case, I hope this is comprehensive enough.
As for the SMALL function... first you need to understand the concept behind the INDEX function.
INDEX can return a value in a specific position of an array, be it the 1st, 2nd, 3rd and so on.
Usually you would go for an INDEX/MATCH combination, but match does not seem to work across multiple columns/rows. To overcome this limitation, we use a combination of SMALL/IF entered as an array.
As you may know, SMALL allows you to select which value to return: the smallest, the 2nd smallest and so on.
Combining it with the IF function in array form, you basically get an array of results matching the criteria (in this case "=1200"). What we are looking for here is the ROW to use in the INDEX, so, IF TRUE, it will return the ROWS where it matches 1200. Using ROW(I1) gives you "1" (first match) and by not being an absolute reference when copied down will return 2 (ROW(I2)), 3 (ROW(I3)) and so on.
The same can be applied to the "Make", only now it will have to match both "=1200" and the service provider already found, hence the "IF(($A$2:$E$10=$H$3)*($A$2:$A$10=$I3)"... * allows for more conditions, similar to AND.
In the end the function comes down to:
"INDEX(array: Service providers or Makes, position: ROW where it finds the match) The position will be the row of the nth value of the array found with the SMALL function.
It is quite hard to explain so I hope I didn't mess up
You can also look it up online or use the evaluation tool in Excel to go through the steps.
In any case, I hope this is comprehensive enough.
Last edited: