Matching a specific cell value in column B then return info from column C

Sorry I didn’t know how to word this question and I don’t think it does my real problem justice
In any case here is what I want to do. Pretend this is what my spreadsheet shows

excel q.png

I want to create a formula in cell A1 that says something like this: if the value of cell b1 = a cell value in column C then return the value of column D in the equivalent row of the matched cell.
So if we were to put this formula in cell a1 it would display the result “Green” because the value 1 in cell B1 matches cell C6, therefore it displays the value of cell d6: green
I also need this formula to not get hung up on duplicates. In column C you will notice two fields contain the number 7 and two fields contain the number 6. Column D will always contain the exact same thing for each respective number in the same row (column C has a 7 the cell in column D for the same row will always be exactly “yellow”.)
The reason I need a formula like this is because I am getting two separate excel sheets each week. The first sheet shows me contract numbers and the customers that are using those contracts but this sheet does not show me the type/category of each contract. The second sheet shows a list of contract numbers as well as the type/category of each contract. Unfortunately I can’t just sort the two lists in ascending order and copy and paste accordingly because my first sheet contains an exceedingly greater number of contracts than the 2nd sheet.
I need a formula that will find the matches and then return the value of what “type” that contract is
NOTE: I was thinking I could copy and paste information from the second sheet onto the first sheet so just use my picture as the circumstances surrounding this situation.

Try this:

=IFERROR(VLOOKUP(B1,$C$1:$D$7,2,0),"No Match")

- Moo

Hey thanks moo that works. Your response triggered a memory of a formula I used a while back. It uses INDEX instead of VLOOKUP in this case the formula would be


=IFERROR(INDEX(D$1:D$7,MATCH(B1,C$1:C$7,0)),"no match")


if not for reply I would never have remembered this.

Thank you

You're welcome, Madmortagan68. Glad I could help!

- Moo

- - - - - - - - - -
If you are satisfied with the solution(s) provided, please mark the thread as [SOLVED] by clicking on the words 'Thread Tools' above your 1st post in this thread. Then select 'Mark Thread as Solved'.

Also, a good way to 'Say thanks' to those who have helped is to click on the star below their name in one of their posts. It's always appreciated.