excel-formulaxlookupindex-match

Search for Value in a table

This has me stumped.

I am working with a scenario where I need to look for a value in a table. I'm familiar with the index-match formula, but I'm having a hard time setting it up because it seems that the formula requires knowing which column the value is it, which defeats the purpose.

I want the formula to check to see if value is there, and if it is, then return the value; otherwise a blank. I included the formula that I'm trying to use but it gives me an error.

See screenshot for issue.

enter image description here

column1 column2 column3 column4 column5
3423 348392 23747 94837 1237
1 348393 23748 94838 1238
3424 348394 23749 94839 1239
3425 348395 23750 94840 1240
3426 348396 23751 94841 1241
3427 348397 23752 94842 1242
3428 348398 23753 94843 1243
3429 348399 23754 94844 1244
3430 348400 23755 94845 1245
3431 348401 23756 94846 1246
986 348402 23757 94847 1247
796675 348403 23758 94848 1248
Solution

  • Try using the following formula:

    enter image description here

    =TOCOL(H3/(H3=A2:E13),2)

    And for all the three together:

    =BYCOL(H3:J3,LAMBDA(x,TOCOL(x/(x=A2:E13),2)))

    You can also use:

    =--CONCAT(REPT(A2:E13,H3=A2:E13))