Excel search in Array and return column

Question

I'm trying to use a value to search a range of rows. Then I need to return a column from that row.

I've been looking into VLOOKUP, but the result has to be within the range, so I don't know how to go from there.

I already managed to check if Sheet 2 A is in sheet 1 A range with the following:

=COUNTIF(Sheet1!A2:A600;Sheet1!A2)

I Would like in sheet 2, in column B, to check if column A is anywhere in sheet 1 column A. If it is, return the matched row and return the column C.

How would I get this?

Sheet 1:

sheet 2:

Erdiansyah · Accepted Answer · 2017-07-21 11:22:35Z

1

Just use VLookup

The formula for VLOOKUP is

=VLOOKUP(search_key,range,column_index,is_sorted)

So in your case, just make sure the range covered all column in sheet1, not just the column A. Column_index will be the index of the column where we look up the value (start from 1), And also is_sorted to be false so the range will not change because of sorting. The formula could be like this:

=VLOOKUP(A2,Sheet1!A2:C6,3,false)

Hope that helps

edited Jul 21, 2017 at 11:22

answered Jul 21, 2017 at 10:52

Erdiansyah

1013 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel search in Array and return column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related