Index with Match help

[rcb007]

I am trying to put together an index and match, but also with translating the word value to number.

=IF(E20="RUFF","YES",IF(E20="-","-","NO")

=(INDEX('Sheet2'!$AL$20:$AL$1000,MATCH($C12,'Sheet2'!$A$20:$A$1000,0)))

To a combined:

=((INDEX('Sheet2'!$AL$20:$AL$1000,MATCH($C12,'Sheet2'!$A$20:$A$1000,0))),IF(E20="RUFF","YES",IF(E20="-","-","NO"))

 

[6StringJazzer]

It's not clear how you want these two apparently unrelated formulas to be combined. Your final formula is not a valid formula so it doesn't help explain your question.

Is the INDEX formula you showed in E20? If not, I cannot begin to guess what you need. What is the relationship between this two formulas and what are you trying to do with a single combined formula?

 

[etaf]

how does C12 relate to E20
Dont really understand what you want to do
can you provide more info

your index match will return a value from sheet2!AL if there is a match in the column sheet2A, if it matches to C12
No idea how that relates to the IF using E20

 

[rcb007]

I have a value on another sheet that is in text "RUFF". In the current sheet I have index match to get that value. But I did not know 'if on the fly' the value could be translated to a number. It sounds like I need to have 2 cells instead of combining it into on formula. I hope that makes some sense.

 

[etaf]

so the result from index/match will always be RUFF ????

OR is it only IF the result is RUFF

IF(INDEX('Sheet2'!$AL$20:$AL$1000,MATCH($C12,'Sheet2'!$A$20:$A$1000,0))="RUFF","YES", IF(INDEX('Sheet2'!$AL$20:$AL$1000,MATCH($C12,'Sheet2'!$A$20:$A$1000,0))="-", "-", "NO"))

 

[rcb007]

You are right, it is supposed to be an if statement. I did plug the formula into the cell near the C12 at D12. However the value is showing a #N/A.

(Sheet1)
C12="1"
C13="2"
C14="3"
D12="=IF(INDEX(Sheet2!$AL$20:$AL$1000,MATCH($C14,Sheet2!$A$20:$A$1000,0))="RUFF","YES", IF(INDEX(Sheet2!$AL$20:$AL$1000,MATCH($C14,Sheet2!$A$20:$A$1000,0))="-", "-", "NO"))"

(Sheet2)
A20="1"
A21="2"
A22="3"
AL20="RUFF"
AL21="-"
AL22="RUFF"

Hope this helps

 

[etaf]

INDEX(Sheet2!$AL$20:$AL$1000,MATCH($C14,Sheet2!$A$20:$A$1000,0))
based on the data set
C14 = "1"

so that matches with Sheet2! A20
and Sheet2!AL20 would return
RUFF
so result would be YES

I have done a sample sheet - all in 1 sheet and column C is instead of column AL, just for presentation here

Book4
	A	B	C	D
13			C14 Lookup	Formula - Using C instead of AL as an example
14			1	YES
15			2	-
16			3	YES
17			4	NO
18
19
20
21
22	Lookup Table
23			Column AL
24	1		RUFF
25	2		-
26	3		RUFF
27	4		fred
28
Sheet2

Cell Formulas
Range	Formula
D14:D17	D14	=IF(INDEX(Sheet2!$C$20:$C$1000,MATCH($C14,Sheet2!$A$20:$A$1000,0))="RUFF","YES", IF(INDEX(Sheet2!$C$20:$C$1000,MATCH($C14,Sheet2!$A$20:$A$1000,0))="-", "-", "NO"))

 

[rcb007]

Awesome! Thank you. It makes sense now. I appreciate it!

 

[etaf]

you are welcome