CODEWITHSUNDEEP
excel
shirley rudd
shirley rudd

Reputation: 31

Excel - match data from one range to another and get the value from the cell to the right of the matched data

I'm not all that great with Excel formulas, and I'm trying to work out how to firstly check if a cell value exists in a column, and if it does, get the value of the next cell.

Specifically, I have a range of cells, from B31 to B39. What I want to do is see if those values occur in cells F3 to F12, and if they do, place the value from the cell directly to the right, ie column G3 to G12, in location D31 to D39.

I've tried IFs statements, match, VLookup and also tried solutions that I found on the net, but it keeps coming back with formula not valid. I've also tried the answer given in Stack Overflow :

Excel: check if cell value exists in column, and then get the value of the next cell

, but I don't really understand the formula so I'm getting lost trying to convert my ranges to the formula given.

Here's the formula I'm trying to use.

=If (ISerror(Match (B31,F:F,0),"no match", Vlookup (b31,F,G,2,false)

Please can anyone help.

Upvotes: 3

Views: 111055

Answers (4)

Shalindra Weliwita
Shalindra Weliwita

Reputation: 1

I have added the following on my excel sheet

=VLOOKUP(B2,Res_partner!$A$2:$C$21208,1,FALSE)

Still doesn't seem to work. I get an #N/A
BUT

=VLOOKUP(B2,Res_partner!$C$2:$C$21208,1,FALSE)

Works

Upvotes: 0

ricohflex
ricohflex

Reputation: 11

Thanks a bundle, guys. You are great.

I used Chuff's answer and modified it a little to do what I wanted.

I have 2 worksheets in the same workbook.

On 1st worksheet I have a list of SMS in 3 columns: phone number, date & time, message

Then I inserted a new blank column next to the phone number

On worksheet 2 I have two columns: phone number, name of person

Used the formula to check the cell on the left, and match against the range in worksheet 2, pick the name corresponding to the number and input it into the blank cell in worksheet 1.

Then just copy the formula down the whole column until last sms It worked beautifully. 

=VLOOKUP(A3,Sheet2!$A$1:$B$31,2,0)

Upvotes: 0

user282148
user282148

Reputation: 11

Here is how I used the formula from chuffs' solution:

In Sheet1, column C5, I have first names from one list and answers to a survey, but no email address. In sheet two, column A1 and C1, I have first names and email addresses, but no answers to the survey. I need email addresses and answers to the survey for my project.

With this formula I was able to get the solution as follows, putting the matched email addresses in column A1 of sheet 1.

=IFERROR(VLOOKUP(C5,Sheet1!$A$2:$C$654,3,0),"")

Upvotes: 1

chuff
chuff

Reputation: 5866

Put this formula in cell d31 and copy down to d39

 =iferror(vlookup(b31,$f$3:$g$12,2,0),"")

Here's what is going on. VLOOKUP:

  • Takes a value (here the contents of b31),
  • Looks for it in the first column of a range (f3:f12 in the range f3:g12), and
  • Returns the value for the corresponding row in a column in that range (in this case, the 2nd column or g3:g12 of the range f3:g12).

As you know, the last argument of VLOOKUP sets the match type, with FALSE or 0 indicating an exact match.

Finally, IFERROR handles the #N/A when VLOOKUP does not find a match.

Upvotes: 5

Related Questions