Look up a value

[Hasse]

Hallo, I am trying to find a formula so when I i.e enter a value from column A or C I want the vaule from column B or D to be shown /Hasse

<tbody>
</tbody>

A B C D

30001	22855	30201	22998
30002	22856	30202	22999
30003	22857	30203	23000
30004	22858	30204	23001
30005	22859	30205	23002
30006	22860	30206	23003
30007	22861	30207	23004
30008	22862	30208	23005
30009	22863	30209	23006
30010	22864	30210	23007
30011	22865	30211	23008
30012	22866	30212	23009
30013	22867	30213	23010
30014	22868	30214	23011
30015	22869	30215	23012
30016	22870	30216	23013
30017	22871	30217	23014

<tbody>
</tbody>

 

[Scott Huish]

In this example, I'm putting the number to look up in G1

=IFERROR(VLOOKUP(G1,$A$1:$B$17,2,0),VLOOKUP(G1,$C$1:$D$17,2,0))

Excel 2010
	A	B	C	D	E	F	G	H
1	30001	22855	30201	22998			30204	23001
2	30002	22856	30202	22999
3	30003	22857	30203	23000
4	30004	22858	30204	23001
5	30005	22859	30205	23002
6	30006	22860	30206	23003
7	30007	22861	30207	23004
8	30008	22862	30208	23005
9	30009	22863	30209	23006
10	30010	22864	30210	23007
11	30011	22865	30211	23008
12	30012	22866	30212	23009
13	30013	22867	30213	23010
14	30014	22868	30214	23011
15	30015	22869	30215	23012
16	30016	22870	30216	23013
17	30017	22871	30217	23014
Sheet1

Cell Formulas
Range	Formula
H1	=IFERROR(VLOOKUP(G1,$A$1:$B$17,2,0),VLOOKUP(G1,$C$1:$D$17,2,0))

 

[Hasse]

Thanks for quick replay. So if I want to extend the table I need to add a vlookup for every extra columns?

/Hasse

 

[jtakw]

Hi,

Responded here: https://www.mrexcel.com/forum/excel-questions/1037888-formula-search-big-table.html

See if this works for you:

Book1
	A	B	C	D	E	F
1	30001	22855	30201	22998	30204	23001
2	30002	22856	30202	22999
3	30003	22857	30203	23000
4	30004	22858	30204	23001
5	30005	22859	30205	23002
6	30006	22860	30206	23003
7	30007	22861	30207	23004
8	30008	22862	30208	23005
9	30009	22863	30209	23006
10	30010	22864	30210	23007
11	30011	22865	30211	23008
12	30012	22866	30212	23009
13	30013	22867	30213	23010
14	30014	22868	30214	23011
15	30015	22869	30215	23012
16	30016	22870	30216	23013
Sheet3

Cell Formulas
Range	Formula
F1	=SUMIF($A$1:$C$16,E1,$B$1:$D$16)

Adjust Column/Cell references to cover your entire Table.

 

[Hasse]

Hallo,
Thanks for helping me with this. Now it works. And yes, I realized when I looked at the other thread that it became confusing

Once again, Big thanks for this. I have worked with it long now and was almost giving up

/Hasse

 

[jtakw]

You're welcome, welcome to the forum, when you get a chance, you might want to check this out:

https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

https://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Out of courtesy, please post back at the other thread that your query is resolved in this thread.

Again, you're welcome.