index match insufficient

[jkicker]

i have a problem that i don't think index match can solve.

I have 2 sets of data, i want to use one set to look up the other set and return a value in the same row but another column. the problem is that the lookup range and the respective return results are not each in one column. For example:

lookup value	range	range	return	return
166058	880507		Name1
167885	558833		Name2
452000		554411		Name3
880507		166058		Name4
		552266		Name5
	452000		Name6
		167885		Name7

<tbody>
</tbody>

What formula, or combination of formulas could I use so that 166058 returns Name4 AND 452000 returns Name6? Things to note: in every row of the data we are searching in, only 3 cells have data in them and there is a consistent spacing of blank cells in between. The lookup value will be the second piece of data in that row, and the return value will be the 3rd.

 

[Scott T]

Book1
	A	B	C	D
1	range	range	return	return
2	880507		Name1
3	558833		Name2
4		554411		Name3
5		166058		Name4
6		552266		Name5
7	452000		Name6
8		167885		Name7
9
10
11
12	lookup value
13	166058	Name4
14	167885	Name7
15	452000	Name6
16	880507	Name1
Sheet4

Cell Formulas
Range	Formula
B13	=IFNA(INDEX($C$2:$C$8,MATCH(A13,$A$2:$A$8,0)),INDEX($D$2:$D$8,MATCH(A13,$B$2:$B$8,0)))

 

[jkicker]

	A	B	C	D
1	range	range	return	return
2	880507		Name1
3	558833		Name2
4		554411		Name3
5		166058		Name4
6		552266		Name5
7	452000		Name6
8		167885		Name7
9
10
11
12	lookup value
13	166058	Name4
14	167885	Name7
15	452000	Name6
16	880507	Name1

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula B13 =IFNA(INDEX($C$2:$C$8,MATCH(A13,$A$2:$A$8,0)),INDEX($D$2:$D$8,MATCH(A13,$B$2:$B$8,0))) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Looks good, but i should have also noted that there are more than 2 columns of range. Can i just keep adding commas to the above? Or just keep nesting IFNA? I'm gonna try both.

 

[jkicker]

So i did it, with 6 nested IFNA. Is there a better way? I just want to search a range (multiple columns) for a value and return the next non-blank value to the right.

 

[Scott T]

You could use VBA.

Sub findname()
Dim lr As Long
Dim rng As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("A2:C9")
For x = 13 To lr
    f = ""
    On Error Resume Next
    f = rng.Find(Cells(x, 1), lookat:=xlWhole).End(xlToRight)
    If f <> "" Then Cells(x, 2) = f
Next x

End Sub

 

[AhoyNC]

Maybe:
This is an array formula and must be entered with CTRL-SHIFT-ENTER.
Drag formula down as needed.

Excel Workbook
	A	B	C	D	E	F	G
1	lookup value	range	range	return	return
2	166058	880507		Name1			Name4
3	167885	558833		Name2			Name7
4	452000		554411		Name3		Name6
5	880507		166058		Name4		Name1
6	552266		552266		Name5		Name5
7		452000		Name6
8			167885		Name7
Sheet