0

I've searched and cannot find the formula or way to get the result I'm looking for in Google Sheets.

I'm using two sheets: ITEMS, RECIPES:

ITEMS has two columns: ITEM_NAME, REF_RECIPE
RECIPES has two columns: RECIPE_NUM, RECIPE_NAME.

I would like to have the REF_RECIPE value in ITEMS auto-populate with the RECIPE_NUM value of the RECIPE_NAME from RECIPES if ITEM_NAME in ITEMS is in the RECIPE_NAME column.

Any guidance would be appreciated. I've added screenshots for reference.

As you can see in the ITEMS screenshot, the current formula isn't working:

=IF( COUNTIF(RECIPES!A$2:B, C2) > 0, RECIPES!A2:A, "" )

The REF_RECIPE values should be:

REF_RECIPE REF_RECIPE
water R0004
boiled egg R0003
cloth R0002
paper R0001
water R0004

Again, any help would be greatly appreciated. Thank you.

ITEMS sheet RECIPES sheet

Improve this question
1
  • vlookup, xlookup, index/match
    – user11222393
    Commented Nov 26, 2023 at 21:03

1 Answer 1

Reset to default
1

Use XLOOKUP() function.

=XLOOKUP(A2,F:F,E:E)

To make it dynamic spill array, use MAP() function.

=MAP(A2:A6,LAMBDA(x,XLOOKUP(x,F:F,E:E)))

enter image description here

Improve this answer
1
  • 1
    Thank you, good sir! XLOOKUP is what I needed. I had to do some additional tweaking to my formula, but it all worked out because of your guidance. I ended up using LET with XLOOKUP, which turned out to work as I needed it. Again, thank you.
    – Lexiriam
    Commented Nov 27, 2023 at 8:00

Not the answer you're looking for? Browse other questions tagged or ask your own question.