Forum Discussion

Copper Contributor

Jun 20, 2023

one thing equals another

I want to type in one box, a choice, 1-5. and each # has a corresponding score. so I want the next box to pull up the score. so essentially if if B3 equals 2, then b4 should be 15; if B3 equals 3, t...

Formulas and Functions

MVP

Jun 20, 2023

Create a lookup range like this (the output values are just examples):

I used E2:F6 but it can be anywhere (including on another sheet).

You can use the input column as source for a data validation drop down if you like.

In B4:

=IFERROR(VLOOKUP(B3, $E$2:$F$6, 2, FALSE), "")

or

=XLOOKUP(B3, $E$2:$E$6, $F$2:$F$6, "")

Susan_1207
Copper Contributor
Jun 20, 2023
I am a super novice... what do the "") mean? Am i literally copying and pasting? so i am using e3-f6 as my input/output and my b2 is where I am putting the 1-5, and my c2 is where I want the correlating 0; 8; 15; 22; 30. down through line 25. what does that look like? thank you so much!!
- HansVogelaar
  MVP
  Jun 20, 2023
  Susan_1207
  
  E3:F6 has only 4 rows. I assume that you mean E2:F6.
  
  "" is the empty text string. The formula will return this if B2 does not equal 1, 2, 3, 4 or 5.
  
  Select cell C2, then enter the following formula in the formula bar, and press Enter:
  
  =XLOOKUP(B2, $E2:$E$6, $F$2:$F$6, "")
  - Susan_1207
    Copper Contributor
    Jun 20, 2023
    HansVogelaar
    
    I am so grateful for your help. I wish i understood excel language better. It just isnt working. i am using e3 to f9. i tried copying that whole line in and I am just getting #### in the line. I separated the e3 with a $ and then got a NAME message... could I send it to you? or is there a way to make this easier? thank you so much