VLOOKUP greatest value of multiple text inputs

[Andreasegeland]

Dear Community,

I am currently trying to do a VLookup and I was hoping you guys could help me save some time. I want to lookup multiple text inputs and return the greatest value among those inputs. I am sure this is a simple task and I am afraid I have over complicated it. This is for a structural engineering purpose where I am trying to find the maximum deflection of beams. The basics behind it is that the primary beams carry the secondary beams. If the primary beams deflect, the secondary will have added deflection to their total deflection. I will be doing a lot of changes to the layout of beams and the design will most likely change, so I need a way to keep track of my total deflection in an efficient way. Therefore, I wish to write a string which I believe would be something like this:

D2= C2+(IF_lookup B1 = SECONDARY, VLOOKUP_E2 (beampair)_return highest value of beams in column A or F corresponding to those listed in beampair E2, IF_lookup B1 = PRIMARY, FALSE/0)

The reason it is complicated is that for example in row 6 column E. There are more than one primary beam, so the total value of deflection D6 needs to have C6 + the highest of those listed in the beam pair. I don't know how it works when you have commas like these and text.

I would appreciate any attempts on this, and I would be happy to learn from this because I have a feeling it can be done in an easier way.

Thank you in advance,

Andreas

[TABLE="width: 1917"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"][/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD]BEAM CALC REF[/TD]
[TD]PRIMARY/SECONDARY[/TD]
[TD]DEFLECTION (mm)[/TD]
[TD]TOTAL DEFLECTION (INC PRIMARY BEAM DEFLECTIONS) (mm)[/TD]
[TD] BEAM PAIR[/TD]
[TD] [/TD]
[TD]BEAM CALC REF [/TD]
[TD]PRIMARY/SECONDARY[/TD]
[TD]DEFLECTION (mm)[/TD]
[TD]TOTAL DEFLECTION (INC PRIMARY BEAM DEFLECTIONS) (mm)[/TD]
[TD]BEAM PAIR[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD]FB01A[/TD]
[TD]SECONDARY[/TD]
[TD]34.3[/TD]
[TD]=C2 + VLOOKUP (FB02, SB03) [/TD]
[TD] FB02[/TD]
[TD][/TD]
[TD]SB01[/TD]
[TD]PRIMARY[/TD]
[TD]5[/TD]
[TD]=F2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD]F B01B[/TD]
[TD]SECONDARY[/TD]
[TD]13.8[/TD]
[TD]=C3 + VLOOKUP [/TD]
[TD] FB34[/TD]
[TD][/TD]
[TD]SB02[/TD]
[TD]PRIMARY[/TD]
[TD] 4[/TD]
[TD]=F3[/TD]
[TD] FB05A[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD]FB02A[/TD]
[TD]PRIMARY[/TD]
[TD]11.1[/TD]
[TD]=C4[/TD]
[TD] FB01A[/TD]
[TD][/TD]
[TD]SB03[/TD]
[TD]PRIMARY[/TD]
[TD]31[/TD]
[TD]=F4[/TD]
[TD] FB01A + FB05A[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]FB02B[/TD]
[TD]PRIMARY[/TD]
[TD]18.9[/TD]
[TD]=C5[/TD]
[TD] SB06[/TD]
[TD][/TD]
[TD] SB04[/TD]
[TD]SECONDARY[/TD]
[TD] 11[/TD]
[TD] =F5 + VLOOKUP[/TD]
[TD] FB34[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]FB05A[/TD]
[TD]SECONDARY[/TD]
[TD]4.2[/TD]
[TD]=C6 + VLOOKUP[/TD]
[TD] SB02, SB03, SB05[/TD]
[TD][/TD]
[TD]SB05[/TD]
[TD]PRIMARY[/TD]
[TD] 12[/TD]
[TD]=F6[/TD]
[TD]FB05A [/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Re: VLOOKUP greatest value of mulitple text inputs

Hi, welcome to the board.

This is probably do-able but I'm struggling to understand your post.

Don't worry too much about explaining the details of why different combinations of beams deflect by different amounts, that's probably not really relevant to the Excel solution.
Focus on what data you have, and what exactly you want to do with it.

I think I get what you want to do with in cell D2, but am struggling with the details.

D2= C2+(IF_lookup B1 = SECONDARY, VLOOKUP_E2 (beampair)_return highest value of beams in column A or F corresponding to those listed in beampair E2, IF_lookup B1 = PRIMARY, FALSE/0)
First, I don't think you need a lookup for the red text above.

I think you can replace this with
D2= C2+(B2 = "SECONDARY", VLOOKUP_E2 (beampair)_return highest value of beams in column A or F corresponding to those listed in beampair E2, IF_lookup B1 = PRIMARY, FALSE/0)

Next, please explain, what exactly you want to look up against on the beams, because it's not clear to me.

 

[Andreasegeland]

Re: VLOOKUP greatest value of mulitple text inputs

Hi Gerald,

Thank you for your response! I agree with you that I dont need the lookup.

I basically want to find the highest deflection value from the text inputs in the "Beam Pair" cell (Column E).

I made it work with:

D2=C2+(IF(B2="SECONDARY",(VLOOKUP(E2,A2:E6,4,FALSE)), 0))

However, my problem now is that if more than one beam is paired, i.e I have FB02, SB06 in cell E2. I want the Vlookup to be written so that it checks the value of both beam FB02 and SB06 and returns me the highest value and adds it to its own deflection.

I can always create another column with the second paired beam and then pick the highest value, but I want to avoid having many columns of data as there might be up to 4 paired beams so it would be better to keep all of them in one cell.

I hope this helps, I am sorry for making it slightly confusing.

Regards,

Andreas

 

[Gerald Higgins]

Re: VLOOKUP greatest value of mulitple text inputs

I see, so cell E2 contains the entry "FB02, SB06", yes ?

And you want to carry out two separate lookups, one on FB02 and one on SB06, and then select one of them based on the results of the lookup, yes ?

That can be done but I would strongly recommend using separate columns for each entry.

That way it will be much easier (in my opinion) to check the formula is delivering the correct results.

If you have two entries in a single cell, then you will have to add complicated formulas which work out which part of the single cell's entry should be dealt with by the first lookup, and which by the second.

 

[Andreasegeland]

Re: VLOOKUP greatest value of mulitple text inputs

Gerald,

Thank you, after spending some time on it I realise it is easier to just add three more columns.

Please let me know if you can think of a more efficient way than this:

=D4+(IF(B4="SECONDARY",(MAX(IF(G4>1,VLOOKUP(G4,A4:K42,6,FALSE),0),(IF(H4>1,VLOOKUP(H4,A4:K42,6,FALSE),0)),(IF(I4>1,VLOOKUP(I4,A4:K42,6,FALSE),0)))),0))

[TABLE="width: 792"]
<colgroup><col span="2"><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]BEAM
CALC REF [/TD]
[TD]PRIMARY/
SECONDARY[/TD]
[TD]STATUS[/TD]
[TD]DEFLECTION (mm)[/TD]
[TD]DEFLECTION FROM IMPOSED (mm)[/TD]
[TD]TOTAL DEFLECTION (INC PRIMARY BEAM DEFLECTIONS) (mm)[/TD]
[TD="colspan: 3"]BEAM PAIR[/TD]
[/TR]
[TR]
[TD]FB01A[/TD]
[TD]SECONDARY[/TD]
[TD]C[/TD]
[TD]34.3[/TD]
[TD]5[/TD]
[TD]53.2[/TD]
[TD]FB02A[/TD]
[TD]FB02B[/TD]
[TD]FB05A[/TD]
[/TR]
[TR]
[TD]FB01B[/TD]
[TD]SECONDARY[/TD]
[TD]C[/TD]
[TD]13.8[/TD]
[TD]6.2[/TD]
[TD]13.8[/TD]
[TD]FB34[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FB02A[/TD]
[TD]PRIMARY[/TD]
[TD]A[/TD]
[TD]11.1[/TD]
[TD]2.3[/TD]
[TD]11.1[/TD]
[TD]FB01A[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FB02B[/TD]
[TD]PRIMARY[/TD]
[TD]A[/TD]
[TD]18.9[/TD]
[TD]6[/TD]
[TD]18.9[/TD]
[TD]SB06[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]FB05A[/TD]
[TD]PRIMARY[/TD]
[TD]A[/TD]
[TD]4.2[/TD]
[TD]2.1[/TD]
[TD]4.2[/TD]
[TD]SB02[/TD]
[TD]SB09[/TD]
[TD]FB05B[/TD]
[/TR]
</tbody>[/TABLE]


Thanks again for your time!

Regards,

Andreas