Searches Right to Left
Friday, June 11, 2010
Getting The Smell Out Of Running Shirts
"I have a table with several columns, whose order I can not change, and I need to search in the second column and give me back what is in the first column. I can not use VLOOKUP because this function performs the search from left to right and I need the opposite. "
To solve this problem we use the functions INDEX and MATCH. We start from the following example:
Table of figure we find that the code is used in column C. We want to introduce one of these codes in cell C2 and C4 that we displayed the name of the employee concerned (search from right to left). To do this follow these steps:
1. We are located in C2 and we menu Data / Validation . Select Allow List and Source mark the range C9: C16, where we have the codes we want to appear on the list. press OK .
2. We are located in cell C4 and type the formula:
= INDEX (B9: B16, MATCH (C2, C9, C16, 0))
portion of the formula MATCH (C2; C9: C16, 0) provides the row number in which the content is within the range C9 C2: C16. If you select the code G17 in cell C2 and tried to introduce this "piece" of formula in a cell, eg C6, we see that we provide the result 7. This is because the code G17 is in row 7 of the range C9: C16. Incorporating this result as the second argument of the INDEX function and obtain the desired solution (search from right to left):
Subscribe to:
Post Comments (Atom)
0 comments:
Post a Comment