CODEWITHSUNDEEP
excelexcel-formula
Leon
Leon

Reputation: 31

Extracting other column value based on lookup

I have one large row of data, containing multiple observations. The data consits of: PO number Amount Location

See attached image:

enter image description here

Now what I want is that PO Number 8382 (A2) is looked up in A8:L8 and puts in B2 the amount of each PO number and puts in C2 the location. So what it needs to do, is lookup a value in the row and extract the value on the right of it. I tried H.lookup but that did not work.

Can anyone help me? I used index and match, but I can't make it work. I believe it's probably a simple question but I can't figure it out.

Thank you in advance!

Upvotes: 1

Views: 97

Answers (3)

Error 1004
Error 1004

Reputation: 8220

You could use:

=IFERROR(INDIRECT(ADDRESS(8,MATCH(A6,$A$8:$H$8,0)+1)),"PO not found")

using IFERROR you avoid receiving errors if any of the PO not found.

enter image description here

Upvotes: 0

shrivallabha.redij
shrivallabha.redij

Reputation: 5902

Your thinking is correct.

In cell B2:

=INDEX($A$8:$L$8,1,MATCH($A2,$A$8:$L$8,0)+1)

In cell C2:

=INDEX($A$8:$L$8,1,MATCH($A2,$A$8:$L$8,0)+2)

Copy down.

Upvotes: 1

Dmitrij Holkin
Dmitrij Holkin

Reputation: 2055

Try this (It find first occurence in ROW 8)

  • Cell B2 formula =OFFSET($A$1;1;MATCH($A2;8:8;0);1;1)
  • Cell C2 formula =OFFSET($A$1;1;MATCH($A2;8:8;0)+1;1;1)

Upvotes: 0

Related Questions