How to find value against color with the help of vlookup in excel?
Answers
Answered by
0
1. In the worksheet contains the value you want to vlookup, right-click the sheet tab and select View Codefrom the context menu. See screenshot:
2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.
VBA code 1: Vlookup and return background color with the lookup value
Then click Insert > Module, and copy the below VBA code 2 into the Module window.
VBA code 2: Vlookup and return background color with the lookup value
After inserting the two codes, then click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot:
5. Press the Alt + Q keys to exit theMicrosoft Visual Basic for Applications window and go back to the worksheet.
6. Select a blank cell adjacent to the lookup value, and then enter formula=LookupKeepColor(E2,$A$1:$C$8,3)into the Formula Bar, and then press the Enter key.
Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need.
7. Keep selecting the first result cell, and drag the Fill Handle down to get all results along with their background color. See screenshot.
2. In the opening Microsoft Visual Basic for Applications window, please copy below VBA code into the Code window.
VBA code 1: Vlookup and return background color with the lookup value
Then click Insert > Module, and copy the below VBA code 2 into the Module window.
VBA code 2: Vlookup and return background color with the lookup value
After inserting the two codes, then click Tools > References. Then check the Microsoft Script Runtime box in the References – VBAProject dialog box. See screenshot:
5. Press the Alt + Q keys to exit theMicrosoft Visual Basic for Applications window and go back to the worksheet.
6. Select a blank cell adjacent to the lookup value, and then enter formula=LookupKeepColor(E2,$A$1:$C$8,3)into the Formula Bar, and then press the Enter key.
Note: In the formula, E2 contains the value you will lookup, $A$1:$C$8 is the table range, and number 3 means that the corresponding value you will return locates in the third column of the table. Please change them as you need.
7. Keep selecting the first result cell, and drag the Fill Handle down to get all results along with their background color. See screenshot.
Similar questions