Thread: Lookup value in one column, match and return result from another column

Hi all, I’m sure this will be simple to some of you clever bods out there but it has stumped me!

I’m trying to get a formula to work where I have a lot of values (many are duplicates) in Column A and I want each one to be matched to its corresponding number in column B and to return the corresponding result from column A in to the cell in column C

It’s really hard to explain (and therefore to find an answer in a search!) – I have attached a file showing the layout and also have highlighted in yellow the expected result and in blue the ‘feeder’ cells

I can’t sort any column other than A and I’ve had a go with lookups etc

Very many thanks in advance

Rae

I am not sure how you got column C? And why is the first one listed 8 times and the second 9 times and so on? Why doesn't column C start with a series of 1 or 1.0.0 for the first item in column B?

Does

=INDEX(A:A,MATCH(C2,B:B,FALSE))

do what you want?

MATCH(C2,B:B,FALSE) tells you which row in the B column the value in C2 can be found
INDEX(A:A,...) then returns the value in the corresponding cell in the A column

NBVC, Sorry, I’m being very blonde and stupid – the column I want the result to go in to is D not C and I have put my expected result in there

In column C the task number could well be repeated many times as there will be other differing data (subtasks) in column E onwards

Columns A & B are a kind of baseline set of data will always be like they are and will be used in other spreadsheets that have different task numbers which may well start 1 or 1.1 or 2.2.2 etc

And HenryCrun, thanks ever so much that certainly seems to work

Then I guess you got your answer....