0

I have a table for seller name and sales

enter image description here

I am matching data for : enter image description here

For Mark I an use simple vlook up formula but for Susan it is returning see other. I want that it should look on other where Mark is assigned for Susan. It should go to again seller column and look for Mark and return 300 in Susan column instead of see other and return 140 for stephan. How can I make formula which can return value for Mark as well as Susan correctly?

trinity
  • 1
  • 1
  • Perhaps use if() and iferror() to swap between vlookup for Mark to Susan. – Solar Mike May 17 '22 at 12:28
  • I'm really struggling to understand your question. How about sharing the vlookup formula that you are currently using and walk us through precisely what you are expecting to see. More than one example would be even better. – CHill60 May 17 '22 at 12:29
  • Please see [mre] and provide an example data set we can copy from, rather than having to manually type out to test on. – Spencer Barnes May 17 '22 at 12:34
  • Also look at [SO Editing help](https://stackoverflow.com/editing-help) to enter a table, rather than attaching images of the data – Spencer Barnes May 17 '22 at 12:36

2 Answers2

0

It's a bit of a confusing table setup, but the below formula should work. Just replace the {name_cell} with whatever cell is pointing to Mark or Susan.

=IF(INDEX(Table1[Sales],MATCH({name_cell},Table1[Seller],0))="see other",
INDEX(Table1[Sales],MATCH(INDEX(Table1[Other],MATCH({name_cell},Table1[Seller],0)),Table1[Seller],0)),
INDEX(Table1[Sales],MATCH({name_cell},Table1[Seller],0)))
Devon Allary
  • 7
  • 1
  • 2
0

Try this formula solution using Vlookup +If function.

In F2, formula copied down :

=IFERROR(VLOOKUP(VLOOKUP(E2,IF({1,0},$B$1:$B$7,$A$1:$A$7),2,0),$B$2:$C$7,2,0),VLOOKUP(E2,$B$2:$C$7,2,0))

enter image description here

bosco_yip
  • 3,762
  • 2
  • 5
  • 10