CODEWITHSUNDEEP
excel
Steven
Steven

Reputation: 181

How to find a row using one column and return the value of that same row in another column

I've been all over excel & the web and the lookup function seems like it should work but it doesn't as far as I can tell.

I have to Excel tables (Table1, Table2)

Both tables have a column named WO

All I want to do is be able to search Table1 to see if it contains a matching WO from table2 then fill the value of another column from Table1.

I've tried LOOKIP, VLOOKUP, .. ive put the WO column on the leftmost side of the table and sorted ascending ... no matter what i've tried the restults are either WRONG or I just get a bunch of #REF or VALUE! errors ...

it seems like a really simple task to me that excel should be able to do .. I mean if I could write it as an SQL query .. it would be as easy as creating an inner join on WO and grabbing the value from the other column ... Why is this so hard to do in excel?

or am I missing something completely ?

Table1

Table2

When I use the Formula ' =LOOKUP([WO Num],Table1[WO'#],Table1[Comment]) ' in the Comment col of Table2 I get the following results

Results from Lookup

Upvotes: 0

Views: 1647

Answers (1)

Ken
Ken

Reputation: 1166

use MATCH and INDEX.

=MATCH(WOValueFromTable2,WOColumnFromTable1,0)

will return the row number of the first matching WO value from Table1

you then can use that with the INDEX function to get the corresponding value:

=INDEX(AnotherColumnFromTable1, MATCH(WOValueFromTable2,WOColumnFromTable1,0))

As an example, this formula entered into Sheet2 B2, would lookup the Value in A1, in Sheet1's Column A, and return the corresponding value from Sheet1's B column

=INDEX(Sheet1!$B:$B,MATCH(Sheet2!$A2,Sheet1!$A:$A,0))

Try this sample in a new spreadsheet.

enter image description here

Type all the values in. The second Size column get the following formula in Cell E2, and then copy it down to E3 and E4

=INDEX(B:B,MATCH(D2,A:A,0))

Upvotes: 1

Related Questions